/**
清空数据库
 */
CALL trading.truncate_database();

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE SCHEMA IF NOT EXISTS trading;

DROP TABLE IF EXISTS trading.dictionary;
CREATE TABLE trading.dictionary(
       pkid SERIAL PRIMARY KEY,
       upid INTEGER DEFAULT NULL,
       code VARCHAR(50) NOT NULL UNIQUE ,
       kind VARCHAR(50) NOT NULL,
       label VARCHAR(50) NOT NULL,
       regexp VARCHAR(100),
       description VARCHAR(200)
);
COMMENT ON TABLE trading.dictionary IS '基础数据字典表';
COMMENT ON COLUMN trading.dictionary.pkid IS '自增主键';
COMMENT ON COLUMN trading.dictionary.upid IS '上级ID';
COMMENT ON COLUMN trading.dictionary.code IS '业务编码';
COMMENT ON COLUMN trading.dictionary.kind IS '字典分类关键字';
COMMENT ON COLUMN trading.dictionary.label IS '字典名称';
COMMENT ON COLUMN trading.dictionary.description IS '描述';

TRUNCATE TABLE trading.dictionary;
INSERT INTO trading.dictionary(code, upid, kind, label, regexp, description)
VALUES ('stock', 0, 'marketplace', '场内证券市场', NULL, NULL),
       ('fund', 0, 'marketplace', '场外基金市场', NULL, NULL),
       ('bond', 0, 'marketplace', '债券市场', NULL, NULL),
       ('sh', 0, 'exchange', '上海证券交易所', NULL, NULL),
       ('sz', 0, 'exchange', '深圳证券交易所', NULL, NULL),
       ('bj', 0, 'exchange', '北京证券交易所', NULL, NULL),
       ('hk', 0, 'exchange', '香港证券交易所', NULL, NULL),
       ('zs.sz.stock', 1, 'stock.type', '深圳指数', '^399\d{3}$', '深圳证券交易所'),
       ('a.sh.stock', 1, 'stock.type', '上证A股', '^60[0135]\d{3}$', '上海证券交易所'),
       ('a.sz.stock', 1, 'stock.type', '深证A股', '^00\d{4}$','深圳证券交易所'),
       ('b.sh.stock', 1, 'stock.type', '上证B股', '^900\d{3}$', '上海证券交易所'),
       ('b.sz.stock', 1, 'stock.type', '深证B股', '^200\d{3}$', '深圳证券交易所'),
       ('a.bj.stock', 1, 'stock.type', '北证A股', '^(43|83|87|88)\d{4}$','北京证券交易所'),
       ('sme.sz.stock', 1, 'stock.type', '中小板', '^002\d{3}$','深圳证券交易所'),
       ('kcb.sh.stock', 1, 'stock.type', '科创板', '^68\d{4}$', '上海证券交易所'),
       ('gem.sz.stock', 1, 'stock.type', '创业板', '^30\d{4}$', '深圳证券交易所'),
       ('hk.stock', 1, 'stock.type', '港股', '\d{4,5}.HK$', '香港证券交易所'),
       ('gzxh.sh.bond', 3, 'stock.type', '上证国债现货', '^001\d{3}$', '上海证券交易所'),
       ('qyzj.sh.bond', 3, 'stock.type', '上证企业债券', '^0[12]0\d{3}$', '上海证券交易所'),
       ('kzhzj.sh.bond', 3, 'stock.type', '上证可转换债券', '^1[20][90]\d{3}$', '上海证券交易所'),
       ('gzhg.sh.bond', 3, 'stock.type', '上证国债回购', '^204\d{3}$', '上海证券交易所'),
       ('gzqh.sh.bond', 3, 'stock.type', '上证国债期货', '^310\d{3}$', '上海证券交易所'),
       ('gzxh.sz.bond', 3, 'stock.type', '深证国债现货', '^10\d{4}$', '深圳证券交易所'),
       ('qyzj.sz.bond', 3, 'stock.type', '深证企业债券', '^11\d{4}$', '深圳证券交易所'),
       ('kzhzj.sz.bond', 3, 'stock.type', '深证可转换债券', '^12\d{4}$', '深圳证券交易所'),
       ('gzhg.sz.bond', 3, 'stock.type', '深证国债回购', '^131\d{3}$', '深圳证券交易所'),
       ('close_end.sh.fund', 3, 'stock.type', '上证封闭式基金', '^508\d{3}$', '上海证券交易所'),
       ('etf.sh.fund', 2, 'stock.type', '上证ETF基金', '^(51|56|58)\d{4}$', '上海证券交易所'),
       ('lof.sh.fund', 2, 'stock.type', '上证LOF基金', '^501\d{3}$', '上海证券交易所'),
       ('fj.sh.fund', 2, 'stock.type', '上证分级基金', '^(502|506)\d{3}$', '上海证券交易所'),
       ('close_end.sz.fund', 2, 'stock.type', '深证封闭式基金', '^184\d{3}$', '深圳证券交易所'),
       ('etf.sz.fund', 2, 'stock.type', '深证ETF基金', '^159\d{3}$', '深圳证券交易所'),
       ('lof.sz.fund', 2, 'stock.type', '深证LOF基金', '^16\d{4}$', '深圳证券交易所'),
       ('fj.sz.fund', 2, 'stock.type', '深证分级基金', '^15\d{4}$', '深圳证券交易所'),
       ('securities.stock', 1, 'securities.type', '场内股票', NULL, NULL),
       ('securities.fund', 1, 'securities.type', '场内基金', NULL, NULL),
       ('securities.bond', 1, 'securities.type', '场内债券', NULL, NULL),
       ('gp.out.fund', 2, 'investment.type', '股票性基金', NULL, NULL),
       ('hh.out.fund', 2, 'investment.type', '混合性基金', NULL, NULL),
       ('zj.out.fund', 2, 'investment.type', '债券性基金', NULL, NULL),
       ('dz.out.fund', 2, 'investment.type', '短债型基金', NULL, NULL),
       ('hb.out.fund', 2, 'investment.type', '货币型基金', NULL, NULL),
       ('etf.out.fund', 2, 'investment.type', '场外ETF基金', NULL, NULL),
       ('lof.out.fund', 2, 'investment.type', '场外LOF基金', NULL, NULL),
       ('qdii.out.fund', 2, 'investment.type', '场外QDII基金', NULL, NULL),
       ('account.stock', 2, 'account.type', '场内证券账户', NULL, NULL),
       ('account.fund', 2, 'account.type', '场外基金账户', NULL, NULL),
       ('account.bank', 2, 'account.type', '现金银行账户', NULL, NULL),
       ('account.loan', 2, 'account.type', '消费信贷账户', NULL, NULL),
       ('bank_dividend', 2, 'bank.trade.type', '银行账户利息', NULL, NULL),
       ('bank_enter', 2, 'bank.trade.type', '转入', NULL, NULL),
       ('bank_out', 2, 'bank.trade.type', '转出', NULL, NULL),
       ('consume.bank_loan', 2, 'loan.trade.type', '消费贷款', NULL, NULL),
       ('consume.bank_pay', 2, 'loan.trade.type', '贷款偿付', NULL, NULL),
       ('subscription', 2, 'investment.trade.type', '认购', NULL, NULL),
       ('apply_purchase', 2, 'investment.trade.type', '申购', NULL, NULL),
       ('fixed_investment', 2, 'investment.trade.type', '定投', NULL, NULL),
       ('fund_dividend', 2, 'investment.trade.type', '红利', NULL, NULL),
       ('redeem', 2, 'investment.trade.type', '赎回', NULL, NULL),
       ('open.holdings', 1, 'stock.trade.holdings', '建仓交易', 'master_t', '主T交易中没有持仓新建仓位'),
       ('add.holdings', 1, 'stock.trade.holdings', '加仓交易', 'master_t', '主T交易中已有仓位增加持有仓位'),
       ('minus.holdings', 1, 'stock.trade.holdings', '减仓交易', 'master_t|slave_t', '主T交易中新增空仓以减少吃有仓位或从T交易中已有仓位减少持有仓位'),
       ('close.holdings', 1, 'stock.trade.holdings', '平仓交易', 'slave_t', '从T交易中平掉主T交易的所有交易量，但可能尚保留持仓量'),
       ('clear.holdings', 1, 'stock.trade.holdings', '清仓交易', 'slave_t', '从T交易中清空所有持仓量'),
       ('long.positions', 1, 'stock.trade.positionDirection', '多头头寸', 'price_action', NULL),
       ('short.positions', 1, 'stock.trade.positionDirection', '空头头寸', 'price_action', NULL),
       ('master_t', 1, 'stock.trade.method', '主T交易', 'price_action', NULL),
       ('slave_t', 1, 'stock.trade.method', '从T交易', 'price_action', NULL),
       ('suggest_entry', 1, 'stock.trade.state', '建议挂单', NULL, NULL),
       ('already_entry', 1, 'stock.trade.state', '已报', NULL, NULL),
       ('already_trading', 1, 'stock.trade.state', '已成交', NULL, NULL),
       ('suggest_repeal', 1, 'stock.trade.state', '建议撤单', NULL, NULL),
       ('already_repeal', 1, 'stock.trade.state', '已撤单', NULL, NULL),
       ('not_trading', 1, 'stock.trade.state', '未成交', NULL, NULL),
       ('buy', 1, 'stock.trade.type', '买入证券', 'stock', NULL),
       ('sell', 1, 'stock.trade.type', '卖出证券', 'stock', NULL),
       ('apply', 1, 'stock.trade.type', '申购证券', 'stock', NULL),
       ('turnIn', 1, 'stock.trade.type', '转入持仓', 'stock', NULL),
       ('turnOut', 1, 'stock.trade.type', '转出持仓', 'stock', NULL),
       ('stockDividend', 1, 'stock.trade.type', '证券股息', NULL, NULL),
       ('shareAllotment', 1, 'stock.trade.type', '证券派股', NULL, NULL),
       ('position', 1, 'stock.trade.type', '调整持仓', NULL, NULL),
       ('bondDividend', 1, 'stock.trade.type', '国债收益', NULL, NULL),
       ('bankDividend', 1, 'stock.trade.type', '账户利息', NULL, NULL),
       ('toStock', 1, 'stock.trade.type', '银转证', NULL, NULL),
       ('toBank', 1, 'stock.trade.type', '证转银', NULL, NULL),
       ('regular_way', 1, 'asset.allocation', '普通交易', NULL, NULL),
       ('trade_left', 1, 'asset.allocation', '左侧逆向', NULL, NULL),
       ('trade_right', 1, 'asset.allocation', '右侧顺势', NULL, NULL),
       ('trade_grid', 1, 'asset.allocation', '网格交易', NULL, NULL),
       ('trade_band', 1, 'asset.allocation', '波段交易', NULL, NULL),
       ('price_action', 1, 'asset.allocation', '价格行为', NULL, NULL),
       ('pa_buy_01', 1, 'buy.price.action', '急速上涨追高做多', 'master_t|slave_t', NULL),
       ('pa_buy_02', 1, 'buy.price.action', '急速上涨低1回调做多', 'master_t|slave_t', NULL),
       ('pa_buy_03', 1, 'buy.price.action', '急速上涨后通道低2做多', 'master_t|slave_t', NULL),
       ('pa_buy_04', 1, 'buy.price.action', '下探对决线卖出高潮时做多', 'master_t|slave_t', NULL),
       ('pa_buy_05', 1, 'buy.price.action', '下探对决线后反转顺势做多', 'master_t|slave_t', NULL),
       ('pa_buy_06', 1, 'buy.price.action', '下探均线后反转顺势做多', 'master_t|slave_t', NULL),
       ('pa_buy_07', 1, 'buy.price.action', '交易区间底部高2突破做多', 'master_t|slave_t', NULL),
       ('pa_buy_08', 1, 'buy.price.action', '交易区间底部低2失败做多', 'master_t|slave_t', NULL),
       ('pa_buy_09', 1, 'buy.price.action', '区间顶部突破回踩再突破做多', 'master_t|slave_t', NULL),
       ('pa_buy_10', 1, 'buy.price.action', '接近前期低点附近做多', 'master_t|slave_t', NULL),
       ('pa_buy_11', 1, 'buy.price.action', '出现卖出高潮或真空衰竭迹象做多', 'master_t|slave_t', NULL),
       ('pa_buy_12', 1, 'buy.price.action', '空仓达到止盈位附近强制平仓或回补', 'slave_t', NULL),
       ('pa_buy_13', 1, 'buy.price.action', '空仓达到移动止损位强制平仓或回补', 'slave_t', NULL),
       ('pa_buy_14', 1, 'buy.price.action', '空仓达到风险回报比目标价位买入', 'slave_t', NULL),
       ('pa_buy_15', 1, 'buy.price.action', '空仓达到心理价位目标时买入', 'slave_t', NULL),
       ('pa_sell_01', 1, 'sell.price.action', '急速下跌追跌做空', 'master_t|slave_t', NULL),
       ('pa_sell_02', 1, 'sell.price.action', '急速下跌高1反弹做空', 'master_t|slave_t', NULL),
       ('pa_sell_03', 1, 'sell.price.action', '急速下跌后通道高2做空', 'master_t|slave_t', NULL),
       ('pa_sell_04', 1, 'sell.price.action', '上探对决线买入高潮时做空', 'master_t|slave_t', NULL),
       ('pa_sell_05', 1, 'sell.price.action', '上探对决线后反转顺势做空', 'master_t|slave_t', NULL),
       ('pa_sell_06', 1, 'sell.price.action', '上探均线后反转顺势做空', 'master_t|slave_t', NULL),
       ('pa_sell_07', 1, 'sell.price.action', '交易区间顶部低2突破做空', 'master_t|slave_t', NULL),
       ('pa_sell_08', 1, 'sell.price.action', '交易区间顶部高2失败做空', 'master_t|slave_t', NULL),
       ('pa_sell_09', 1, 'sell.price.action', '区间底部突破回踩再突破做空', 'master_t|slave_t', NULL),
       ('pa_sell_10', 1, 'sell.price.action', '接近前期高点附近做空', 'master_t|slave_t', NULL),
       ('pa_sell_11', 1, 'sell.price.action', '出现买入高潮或真空衰竭迹象做空', 'master_t|slave_t', NULL),
       ('pa_sell_12', 1, 'sell.price.action', '多仓达到止盈位附近强制平仓或减仓', 'slave_t', NULL),
       ('pa_sell_13', 1, 'sell.price.action', '多仓达到移动止损位强制平仓或减仓', 'slave_t', NULL),
       ('pa_sell_14', 1, 'sell.price.action', '多仓达到风险回报比目标价位卖出', 'slave_t', NULL),
       ('pa_sell_15', 1, 'sell.price.action', '多仓达到心理价位目标时卖出', 'slave_t', NULL);

/**
	查询字典数据
	SELECT stock.func_search_dictionary(json_build_object('pkid', '1'));
	SELECT stock.func_search_dictionary(json_build_object('code', 'stock'));
	SELECT stock.func_search_dictionary(json_build_object('kind', 'stock.type'));
	SELECT stock.func_search_dictionary(json_build_object('upid', '1'));
	SELECT stock.func_search_dictionary(json_build_object('regexp', '600009'));
 */
DROP FUNCTION IF EXISTS trading.func_search_dictionary(JSON);
CREATE OR REPLACE FUNCTION trading.func_search_dictionary(IN dict JSON) RETURNS JSON
AS $$
DECLARE
    pgrn_MESSAGE TEXT;
    pgrn_DETAIL TEXT;
    pgrn_HINT TEXT;
    pgrn_CONTEXT TEXT;
    pgrn_SQLSTATE TEXT;
    dictionarys JSON DEFAULT NULL;
BEGIN
    IF (json_extract_path_text(dict, 'pkid') IS NOT NULL) THEN
        SELECT row_to_json(diction) INTO dictionarys FROM trading.dictionary diction WHERE pkid = json_extract_path_text(dict, 'pkid')::INTEGER;
    ELSEIF (json_extract_path_text(dict, 'code') IS NOT NULL) THEN
        SELECT row_to_json(diction) INTO dictionarys FROM trading.dictionary diction WHERE code = json_extract_path_text(dict, 'code');
    ELSEIF (json_extract_path_text(dict, 'kind') IS NOT NULL) THEN
        SELECT json_agg(diction) INTO dictionarys FROM trading.dictionary diction WHERE kind = json_extract_path_text(dict, 'kind');
    ELSEIF (json_extract_path_text(dict, 'upid') IS NOT NULL) THEN
        SELECT json_agg(diction) INTO dictionarys FROM trading.dictionary diction WHERE upid = json_extract_path_text(dict, 'upid')::INTEGER;
    ELSEIF (json_extract_path_text(dict, 'regexp') IS NOT NULL) THEN
        SELECT row_to_json(diction) INTO dictionarys FROM trading.dictionary diction WHERE json_extract_path_text(dict, 'regexp') ~ regexp LIMIT 1;
    ELSE
        RAISE EXCEPTION '查询字典数据操作失败' USING HINT =$1, DETAIL = '给定的参数不合法';
  END IF;
  IF(dictionarys IS NOT NULL) THEN
    RETURN json_build_object('type', 'Success', 'message', '查询字典数据作操作成功!', 'code', 200, 'data', dictionarys);
  ELSE
    RETURN json_build_object('type', 'Error', 'message', '查询字典数据操作失败!', 'error', '没有找到指定的数据', 'sqlstate', 230);
  END IF;
EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS pgrn_MESSAGE = MESSAGE_TEXT, pgrn_DETAIL = PG_EXCEPTION_DETAIL, pgrn_HINT = PG_EXCEPTION_HINT, pgrn_CONTEXT = PG_EXCEPTION_CONTEXT, pgrn_SQLSTATE = RETURNED_SQLSTATE;
        RAISE NOTICE 'MESSAGE: %', pgrn_MESSAGE;
        RAISE NOTICE 'DETAIL:　%', pgrn_DETAIL;
        RAISE NOTICE 'HINT:　%', pgrn_HINT;
        RAISE NOTICE 'CONTEXT:　%', pgrn_CONTEXT;
        RAISE NOTICE 'SQLSTATE:　%', pgrn_SQLSTATE;
END;
$$ LANGUAGE plpgsql;
COMMENT ON FUNCTION trading.func_search_dictionary(JSON) IS '查询字典数据';

/**
  查询字典数据
	SELECT stock.func_search_dictionary('pkid', '1');
	SELECT stock.func_search_dictionary('code', 'stock');
	SELECT stock.func_search_dictionary('kind', 'stock.type');
	SELECT stock.func_search_dictionary('upid', '1');
	SELECT stock.func_search_dictionary('regexp', '600009');
 */
DROP FUNCTION IF EXISTS trading.func_search_dictionary(VARCHAR, VARCHAR);
CREATE OR REPLACE FUNCTION trading.func_search_dictionary(IN dict VARCHAR, IN vals VARCHAR) RETURNS JSON
AS $$
DECLARE
    pgrn_MESSAGE TEXT;
    pgrn_DETAIL TEXT;
    pgrn_HINT TEXT;
    pgrn_CONTEXT TEXT;
    pgrn_SQLSTATE TEXT;
    dictionarys JSON DEFAULT NULL;
BEGIN
  SELECT trading.func_search_dictionary(json_build_object(dict, vals)) INTO dictionarys;
  RETURN dictionarys;
  EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS pgrn_MESSAGE = MESSAGE_TEXT, pgrn_DETAIL = PG_EXCEPTION_DETAIL, pgrn_HINT = PG_EXCEPTION_HINT, pgrn_CONTEXT = PG_EXCEPTION_CONTEXT, pgrn_SQLSTATE = RETURNED_SQLSTATE;
        RAISE NOTICE 'MESSAGE: %', pgrn_MESSAGE;
        RAISE NOTICE 'DETAIL:　%', pgrn_DETAIL;
        RAISE NOTICE 'HINT:　%', pgrn_HINT;
        RAISE NOTICE 'CONTEXT:　%', pgrn_CONTEXT;
        RAISE NOTICE 'SQLSTATE:　%', pgrn_SQLSTATE;
END;
$$ LANGUAGE plpgsql;
COMMENT ON FUNCTION trading.func_search_dictionary(VARCHAR, VARCHAR) IS '查询字典数据';

DROP TABLE IF EXISTS trading.stock;
CREATE TABLE trading.stock(
      uuid VARCHAR(36) PRIMARY KEY DEFAULT uuid_generate_v4(),
      stock_code VARCHAR(10) NOT NULL UNIQUE,
      stock_type VARCHAR(50),
      stock_label VARCHAR(100) NOT NULL,
      stock_market VARCHAR(50),
      stock_securities VARCHAR(50),
      stock_industry VARCHAR(50),
      stock_area VARCHAR(50),
      tradable_share DECIMAL(8, 2),
      total_share DECIMAL(8, 2),
      date_listing VARCHAR(10),
      info_url VARCHAR(200)
);
COMMENT ON TABLE trading.stock IS '股票和基金数据表';
COMMENT ON COLUMN trading.stock.uuid IS '自增主键';
COMMENT ON COLUMN trading.stock.stock_code IS '股票或基金代码';
COMMENT ON COLUMN trading.stock.stock_type IS '股票、基金或债券';
COMMENT ON COLUMN trading.stock.stock_label IS '股票或基金名称';
COMMENT ON COLUMN trading.stock.stock_market IS '股票或基金所属市场';
COMMENT ON COLUMN trading.stock.stock_securities IS '股票或基金所属类型';
COMMENT ON COLUMN trading.stock.stock_industry IS '股票或基金所属行业';
COMMENT ON COLUMN trading.stock.stock_area IS '股票或基金所属地域';
COMMENT ON COLUMN trading.stock.tradable_share IS '流通股（亿股）';
COMMENT ON COLUMN trading.stock.total_share IS '总股本（亿股）';
COMMENT ON COLUMN trading.stock.date_listing IS '上市日期';
COMMENT ON COLUMN trading.stock.info_url IS '股票或基金F10地址';

CREATE OR REPLACE FUNCTION trading.trigger_insert_before_stock_function()
  RETURNS TRIGGER AS $$
DECLARE
    pgrn_MESSAGE TEXT;
    pgrn_DETAIL TEXT;
    pgrn_HINT TEXT;
    pgrn_CONTEXT TEXT;
    pgrn_SQLSTATE TEXT;
BEGIN
  IF (NEW.stock_code IS NOT NULL AND NEW.stock_label IS NOT NULL) THEN
    IF ((SELECT count(1) FROM trading.stock WHERE stock_code = NEW.stock_code) = 0) THEN
        SELECT code, label, description INTO NEW.stock_securities, NEW.stock_type, NEW.stock_market FROM trading.dictionary
        WHERE NEW.stock_code ~ regexp AND kind = 'stock.type' LIMIT 1;
        NEW.info_url := CONCAT('http://quote.eastmoney.com/', split_part(NEW.stock_securities, '.', 2), NEW.stock_code, '.html');
    END IF;
  END IF;
  RETURN NEW;
  EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS pgrn_MESSAGE = MESSAGE_TEXT, pgrn_DETAIL = PG_EXCEPTION_DETAIL, pgrn_HINT = PG_EXCEPTION_HINT, pgrn_CONTEXT = PG_EXCEPTION_CONTEXT, pgrn_SQLSTATE = RETURNED_SQLSTATE;
        RAISE NOTICE 'MESSAGE: %', pgrn_MESSAGE;
        RAISE NOTICE 'DETAIL:　%', pgrn_DETAIL;
        RAISE NOTICE 'HINT:　%', pgrn_HINT;
        RAISE NOTICE 'CONTEXT:　%', pgrn_CONTEXT;
        RAISE NOTICE 'SQLSTATE:　%', pgrn_SQLSTATE;
END;
$$ LANGUAGE plpgsql;
COMMENT ON FUNCTION trading.trigger_insert_before_stock_function() IS '【股票和基金数据表】的 AFTER 触发器函数';

CREATE TRIGGER trigger_insert_before_stock BEFORE INSERT
  ON trading.stock FOR EACH ROW EXECUTE PROCEDURE trading.trigger_insert_before_stock_function();
COMMENT ON TRIGGER trigger_insert_before_stock ON trading.stock IS '【股票和基金数据表】的 AFTER 触发器';

DROP TABLE IF EXISTS trading.owner;
CREATE TABLE trading.owner(
      uuid VARCHAR(36) PRIMARY KEY DEFAULT uuid_generate_v4(),
      username VARCHAR(50) NOT NULL UNIQUE,
      password VARCHAR(200) NOT NULL,
      realname VARCHAR(50) NOT NULL,
      idcard VARCHAR(18) NOT NULL UNIQUE,
      mobile VARCHAR(11) NOT NULL UNIQUE,
      email VARCHAR(100) NOT NULL UNIQUE
);
COMMENT ON TABLE trading.owner IS '所有人数据表';
COMMENT ON COLUMN trading.owner.uuid IS '主键';
COMMENT ON COLUMN trading.owner.username IS '用户名';
COMMENT ON COLUMN trading.owner.password IS '用户密码';
COMMENT ON COLUMN trading.owner.realname IS '真实姓名';
COMMENT ON COLUMN trading.owner.idcard IS '身份证号';
COMMENT ON COLUMN trading.owner.mobile IS '手机号码';
COMMENT ON COLUMN trading.owner.email IS '电子邮件';

CREATE OR REPLACE FUNCTION trading.func_user_password_check(VARCHAR, VARCHAR)
  RETURNS JSON AS $$
DECLARE
    pgrn_MESSAGE TEXT;
    pgrn_DETAIL TEXT;
    pgrn_HINT TEXT;
    pgrn_CONTEXT TEXT;
    pgrn_SQLSTATE TEXT;
    rowJson JSON DEFAULT NULL;
    rowNum INTEGER DEFAULT 0;
BEGIN
    CASE
        WHEN ($1 ~ '^\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*$') THEN
            SELECT count(1) INTO rowNum FROM trading.owner WHERE email = $1;
            IF (rowNum !=0) THEN
                SELECT row_to_json(t) INTO rowJson FROM (SELECT uuid, username, realname, idcard, mobile, email FROM trading.owner where email = $1 AND password = crypt($2, password)) t;
            ELSE
                RETURN json_build_object('status', 'Error', 'msg', '用户电子邮箱不存在！');
            END IF;
        WHEN ($1 ~ '^(13[0-9]|14[5|7]|15[0|1|2|3|5|6|7|8|9]|18[0|1|2|3|5|6|7|8|9])\d{8}$') THEN
            SELECT count(1) INTO rowNum FROM trading.owner WHERE mobile = $1;
            IF (rowNum !=0) THEN
                SELECT row_to_json(t) INTO rowJson FROM (SELECT uuid, username, realname, idcard, mobile, email FROM trading.owner where mobile = $1 AND password = crypt($2, password)) t;
            ELSE
                RETURN json_build_object('status', 'Error', 'msg', '用户手机号码不存在！');
            END IF;
        WHEN ($1 ~ '^[1-9]\d{5}(18|19|([23]\d))\d{2}((0[1-9])|(10|11|12))(([0-2][1-9])|10|20|30|31)\d{3}[0-9Xx]$') THEN
            SELECT count(1) INTO rowNum FROM trading.owner WHERE idcard = $1;
            IF (rowNum !=0) THEN
                SELECT row_to_json(t) INTO rowJson FROM (SELECT uuid, username, realname, idcard, mobile, email FROM trading.owner where idcard = $1 AND password = crypt($2, password)) t;
            ELSE
                RETURN json_build_object('status', 'Error', 'msg', '用户身份证号码不存在！');
            END IF;
        ELSE
            SELECT count(1) INTO rowNum FROM trading.owner WHERE username = $1;
            IF (rowNum !=0) THEN
                SELECT row_to_json(t) INTO rowJson FROM (SELECT uuid, username, realname, idcard, mobile, email FROM trading.owner where username = $1 AND password = crypt($2, password)) t;
            ELSE
                RETURN json_build_object('status', 'Error', 'msg', '用户名不存在！');
            END IF;
    END CASE;
    IF (rowJson IS NOT NULL ) THEN
        RETURN json_build_object('status', 'Success', 'data', rowJson);
    ELSE
        RETURN json_build_object('status', 'Error', 'msg', '用户密码错误！');
    END IF;
    EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS pgrn_MESSAGE = MESSAGE_TEXT, pgrn_DETAIL = PG_EXCEPTION_DETAIL, pgrn_HINT = PG_EXCEPTION_HINT, pgrn_CONTEXT = PG_EXCEPTION_CONTEXT, pgrn_SQLSTATE = RETURNED_SQLSTATE;
        RAISE NOTICE 'MESSAGE: %', pgrn_MESSAGE;
        RAISE NOTICE 'DETAIL:　%', pgrn_DETAIL;
        RAISE NOTICE 'HINT:　%', pgrn_HINT;
        RAISE NOTICE 'CONTEXT:　%', pgrn_CONTEXT;
        RAISE NOTICE 'SQLSTATE:　%', pgrn_SQLSTATE;
END;
$$ LANGUAGE plpgsql;
COMMENT ON FUNCTION trading.func_user_password_check(VARCHAR, VARCHAR) IS '用户密码校验';

INSERT INTO trading.owner(username, password, realname, idcard, mobile, email)
VALUES ('GadflyBSD', crypt('xx123456', gen_salt('bf')), '刘海明', '530103197510290651', '18100882830', 'gadflybsd@gmail.com');

DROP TABLE IF EXISTS trading.account;
CREATE TABLE trading.account(
      uuid VARCHAR(36) PRIMARY KEY DEFAULT uuid_generate_v4(),
      owner_id VARCHAR(36) NOT NULL REFERENCES trading.owner(uuid),
      account_type VARCHAR(50) NOT NULL REFERENCES trading.dictionary(code),
      account_label VARCHAR(50) NOT NULL,
      balance DECIMAL(9, 2) DEFAULT 0.00,
      stamp_duty_rates DECIMAL(9, 5) DEFAULT 0.001,
      transfer_fee_rates DECIMAL(9, 5) DEFAULT 0.00002,
      stock_rates DECIMAL(9, 5),
      stock_is_free5 BOOLEAN DEFAULT FALSE,
      fund_rates DECIMAL(9, 5),
      fund_is_free5 BOOLEAN DEFAULT TRUE,
      is_default BOOLEAN DEFAULT FALSE
);
COMMENT ON TABLE trading.account IS '账户数据表';
COMMENT ON COLUMN trading.account.uuid IS '自增主键';
COMMENT ON COLUMN trading.account.owner_id IS '账户所有人ID';
COMMENT ON COLUMN trading.account.account_type IS '账户类型 FOR 【account.type】';
COMMENT ON COLUMN trading.account.account_label IS '账户名称';
COMMENT ON COLUMN trading.account.balance IS '账户余额';
COMMENT ON COLUMN trading.account.stamp_duty_rates IS '证券交易印花税率';
COMMENT ON COLUMN trading.account.transfer_fee_rates IS '上证过户费费率';
COMMENT ON COLUMN trading.account.stock_rates IS '股票交易手续费率';
COMMENT ON COLUMN trading.account.stock_is_free5 IS '股票是否免5';
COMMENT ON COLUMN trading.account.fund_rates IS '基金费率';
COMMENT ON COLUMN trading.account.fund_is_free5 IS '基金是否免5';
COMMENT ON COLUMN trading.account.is_default IS '是否默认';

INSERT INTO trading.account(owner_id, account_type, account_label, balance, stock_rates, stock_is_free5, fund_rates, fund_is_free5)
SELECT uuid, 'account.stock', '招商证券', 0, 0.0003, FALSE, 0.003, FALSE FROM trading.owner WHERE username = 'GadflyBSD';
INSERT INTO trading.account(owner_id, account_type, account_label, balance, stock_rates, stock_is_free5, fund_rates, fund_is_free5, is_default)
SELECT uuid, 'account.stock', '海通证券', 0, 0.00012, FALSE, 0.00005, TRUE, TRUE FROM trading.owner WHERE username = 'GadflyBSD';
INSERT INTO trading.account(owner_id, account_type, account_label, balance, stock_rates, stock_is_free5, fund_rates, fund_is_free5, is_default)
SELECT uuid, 'account.fund', '好买基金', 0, 0, FALSE, 0.015, FALSE, TRUE FROM trading.owner WHERE username = 'GadflyBSD';
INSERT INTO trading.account(owner_id, account_type, account_label, balance, stock_rates, stock_is_free5, fund_rates, fund_is_free5)
SELECT uuid, 'account.fund', '蚂蚁财富', 0, 0, FALSE, 0.015, TRUE FROM trading.owner WHERE username = 'GadflyBSD';
INSERT INTO trading.account(owner_id, account_type, account_label, balance, stock_rates, stock_is_free5, fund_rates, fund_is_free5)
SELECT uuid, 'account.fund', '天天基金', 0, 0, FALSE, 0.015, TRUE FROM trading.owner WHERE username = 'GadflyBSD';
INSERT INTO trading.account(owner_id, account_type, account_label, balance, is_default)
SELECT uuid, 'account.bank', '招商银行', 0, TRUE FROM trading.owner WHERE username = 'GadflyBSD';
INSERT INTO trading.account(owner_id, account_type, account_label, balance, is_default)
SELECT uuid, 'account.loan', '招商信用卡', 0, TRUE FROM trading.owner WHERE username = 'GadflyBSD';
INSERT INTO trading.account(owner_id, account_type, account_label, balance)
SELECT uuid, 'account.loan', '蚂蚁花呗', 0 FROM trading.owner WHERE username = 'GadflyBSD';

DROP FUNCTION IF EXISTS trading.func_get_default_account(VARCHAR, VARCHAR);
CREATE OR REPLACE FUNCTION trading.func_get_default_account(VARCHAR, VARCHAR)
  RETURNS JSON
AS $$
DECLARE
    pgrn_MESSAGE TEXT;
    pgrn_DETAIL TEXT;
    pgrn_HINT TEXT;
    pgrn_CONTEXT TEXT;
    pgrn_SQLSTATE TEXT;
    owner_id VARCHAR DEFAULT NULL;
    rowJson JSON DEFAULT NULL;
    rowNum INTEGER DEFAULT 0;
BEGIN
    CASE
        WHEN ($1 ~ '^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$') THEN
            SELECT count(1) INTO rowNum FROM trading.account WHERE owner_id = $1;
            IF (rowNum !=0) THEN
                SELECT row_to_json(t) INTO rowJson FROM (SELECT uuid, account_label, balance, stamp_duty_rates, transfer_fee_rates, stock_rates, stock_is_free5, fund_rates, fund_is_free5, is_default
                FROM trading.account where owner_id = $1 AND is_default = true AND account_type = $2) t;
            ELSE
                RETURN json_build_object('status', 'Error', 'msg', '指定用户的UUID不存在对应的默认账户信息！');
            END IF;
        WHEN ($1 ~ '^\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*$') THEN
            SELECT count(1) INTO rowNum FROM trading.owner WHERE email = $1;
            IF (rowNum !=0) THEN
                SELECT row_to_json(t) INTO rowJson FROM (SELECT a.uuid, a.account_type, a.account_label, a.balance, a.stamp_duty_rates, a.transfer_fee_rates,
                                                                a.stock_rates, a.stock_is_free5, a.fund_rates, a.fund_is_free5, a.is_default
                FROM trading.account a, trading.owner o WHERE a.owner_id = o.uuid AND o.email = $1 AND a.is_default IS TRUE AND a.account_type = $2) t;
            ELSE
                RETURN json_build_object('status', 'Error', 'msg', '指定用户的电子邮箱不存在对应的默认账户信息！');
            END IF;
        WHEN ($1 ~ '^(13[0-9]|14[5|7]|15[0|1|2|3|5|6|7|8|9]|18[0|1|2|3|5|6|7|8|9])\d{8}$') THEN
            SELECT count(1) INTO rowNum FROM trading.owner WHERE mobile = $1;
            IF (rowNum !=0) THEN
                SELECT row_to_json(t) INTO rowJson FROM (SELECT a.uuid, a.account_type, a.account_label, a.balance, a.stamp_duty_rates, a.transfer_fee_rates,
                                                                a.stock_rates, a.stock_is_free5, a.fund_rates, a.fund_is_free5, a.is_default
                FROM trading.account a, trading.owner o WHERE a.owner_id = o.uuid AND o.mobile = $1 AND a.is_default IS TRUE AND a.account_type = $2) t;
            ELSE
                RETURN json_build_object('status', 'Error', 'msg', '指定用户的手机号码不存在对应的默认账户信息！');
            END IF;
        WHEN ($1 ~ '^[1-9]\d{5}(18|19|([23]\d))\d{2}((0[1-9])|(10|11|12))(([0-2][1-9])|10|20|30|31)\d{3}[0-9Xx]$') THEN
            SELECT count(1) INTO rowNum FROM trading.owner WHERE idcard = $1;
            IF (rowNum !=0) THEN
                SELECT row_to_json(t) INTO rowJson FROM (SELECT a.uuid, a.account_type, a.account_label, a.balance, a.stamp_duty_rates, a.transfer_fee_rates,
                                                                a.stock_rates, a.stock_is_free5, a.fund_rates, a.fund_is_free5, a.is_default
                FROM trading.account a, trading.owner o WHERE a.owner_id = o.uuid AND o.idcard = $1 AND a.is_default IS TRUE AND a.account_type = $2) t;
            ELSE
                RETURN json_build_object('status', 'Error', 'msg', '指定用户的身份证号码不存在对应的默认账户信息！');
            END IF;
        ELSE
            SELECT count(1) INTO rowNum FROM trading.owner WHERE username = $1;
            IF (rowNum !=0) THEN
                SELECT row_to_json(t) INTO rowJson FROM (SELECT a.uuid, a.account_type, a.account_label, a.balance, a.stamp_duty_rates, a.transfer_fee_rates,
                                                                a.stock_rates, a.stock_is_free5, a.fund_rates, a.fund_is_free5, a.is_default
                FROM trading.account a, trading.owner o WHERE a.owner_id = o.uuid AND o.username = $1 AND a.is_default IS TRUE AND a.account_type = $2) t;
            ELSE
                RETURN json_build_object('status', 'Error', 'msg', '指定用户的用户名不存在对应的默认账户信息！');
            END IF;
    END CASE;
    RETURN json_build_object('status', 'Success', 'data', rowJson);
    EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS pgrn_MESSAGE = MESSAGE_TEXT, pgrn_DETAIL = PG_EXCEPTION_DETAIL, pgrn_HINT = PG_EXCEPTION_HINT, pgrn_CONTEXT = PG_EXCEPTION_CONTEXT, pgrn_SQLSTATE = RETURNED_SQLSTATE;
        RAISE NOTICE 'MESSAGE: %', pgrn_MESSAGE;
        RAISE NOTICE 'DETAIL:　%', pgrn_DETAIL;
        RAISE NOTICE 'HINT:　%', pgrn_HINT;
        RAISE NOTICE 'CONTEXT:　%', pgrn_CONTEXT;
        RAISE NOTICE 'SQLSTATE:　%', pgrn_SQLSTATE;
END
$$ LANGUAGE plpgsql;
COMMENT ON FUNCTION trading.func_get_default_account(VARCHAR, VARCHAR) IS '获取指定用户默认账户信息';

DROP TABLE IF EXISTS trading.details_price;
CREATE TABLE trading.details_price(
       uuid VARCHAR(36) PRIMARY KEY DEFAULT uuid_generate_v4(),
       stock_code VARCHAR(6) REFERENCES trading.stock(stock_code) NOT NULL,
       open DECIMAL(9, 3) DEFAULT 0.000,
       close DECIMAL(9, 3) DEFAULT 0.000,
       high DECIMAL(9, 3) DEFAULT 0.000,
       low DECIMAL(9, 3) DEFAULT 0.000,
       volume DECIMAL(15, 2) DEFAULT 0.00,
       number INTEGER NOT NULL,
       period VARCHAR(6) NOT NULL,
       revolution VARCHAR(10) NOT NULL,
       datetime TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL,
       insert_time TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NOW()
);
COMMENT ON TABLE trading.details_price IS '市场周期收盘数据';
COMMENT ON COLUMN trading.details_price.uuid IS '主键';
COMMENT ON COLUMN trading.details_price.stock_code IS '股票或基金代码';
COMMENT ON COLUMN trading.details_price.open IS '开盘价';
COMMENT ON COLUMN trading.details_price.close IS '收盘价';
COMMENT ON COLUMN trading.details_price.high IS '最高价';
COMMENT ON COLUMN trading.details_price.low IS '最低价';
COMMENT ON COLUMN trading.details_price.volume IS '成交量';
COMMENT ON COLUMN trading.details_price.number IS '当前持仓数量';
COMMENT ON COLUMN trading.details_price.period IS '结算周期：day、week 或 month';
COMMENT ON COLUMN trading.details_price.revolution IS '收盘时间周期';
COMMENT ON COLUMN trading.details_price.datetime IS '收盘时间';
COMMENT ON COLUMN trading.details_price.insert_time IS '记录时间';

CREATE OR REPLACE FUNCTION trading.isleap(integer)
  RETURNS BOOLEAN
AS $$
select (($1 % 4) = 0 and (($1 % 100) <> 0 or ($1 % 400) = 0))
$$ LANGUAGE SQL IMMUTABLE STRICT;
COMMENT ON FUNCTION trading.isleap(integer) IS '判断年份是闰年还是平年';

CREATE OR REPLACE FUNCTION trading.intervalDay(date)
  RETURNS INTEGER
AS $$
select (extract(epoch from $1) / 86400)::integer;
$$ LANGUAGE SQL IMMUTABLE STRICT;
COMMENT ON FUNCTION trading.intervalDay(date) IS '与1970-1-1间隔多少天';

CREATE OR REPLACE FUNCTION trading.getDateWeek(date)
  RETURNS INTEGER
as $$
with cte as (
  select ( ( 4 + trading.intervalDay($1) ) % 7 ) as val
) select ( case when val < 0 then  7+val else val end)
from cte
$$ LANGUAGE SQL IMMUTABLE STRICT;
COMMENT ON FUNCTION trading.getDateWeek(date) IS '获取日期是星期几';

CREATE OR REPLACE FUNCTION trading.getMonMaxDate(iyear integer, imonth integer)
  RETURNS date
AS $$
select make_date( iyear,imonth,
                  (case when 2=$2 then (case when trading.isleap($1) then 29 else 28 end)
                        when 4=$2 or 6=$2 or 9=$2 or 11=$2 then 30 else 31 end));
$$ LANGUAGE SQL IMMUTABLE STRICT;
COMMENT ON FUNCTION trading.getMonMaxDate(iyear integer, imonth integer) IS '根据年度和月份获取本月的最后一天';

DROP TABLE IF EXISTS trading.position;
CREATE TABLE trading.position(
       uuid VARCHAR(36) PRIMARY KEY DEFAULT uuid_generate_v4(),
       account_id VARCHAR(36) REFERENCES trading.account(uuid) NOT NULL,
       stock_code VARCHAR(6) REFERENCES trading.stock(stock_code) NOT NULL,
       capitalized DECIMAL(9, 3),
       market_value DECIMAL(9, 3),
       number INTEGER,
       initial_date DATE,
       last_datetime TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NOW()
);
COMMENT ON TABLE trading.position IS '证券及ETF持仓统计表';
COMMENT ON COLUMN trading.position.uuid IS '主键';
COMMENT ON COLUMN trading.position.account_id IS '交易账户ID';
COMMENT ON COLUMN trading.position.stock_code IS '股票或基金代码';
COMMENT ON COLUMN trading.position.capitalized IS '持仓成本';
COMMENT ON COLUMN trading.position.market_value IS '市场价格';
COMMENT ON COLUMN trading.position.number IS '持仓数量';
COMMENT ON COLUMN trading.position.initial_date IS '初始建仓日期';
COMMENT ON COLUMN trading.position.last_datetime IS '最后更新时间';

DROP TABLE IF EXISTS trading.asset;
CREATE TABLE trading.asset(
       uuid VARCHAR(36) PRIMARY KEY DEFAULT uuid_generate_v4(),
       account_id VARCHAR(36) REFERENCES trading.account(uuid) NOT NULL,
       stock_code VARCHAR(6) REFERENCES trading.stock(stock_code) NOT NULL,
       allocation VARCHAR(50) REFERENCES trading.dictionary(code),
       number INTEGER
);
COMMENT ON TABLE trading.asset IS '资产配置组合统计表';
COMMENT ON COLUMN trading.asset.uuid IS '主键';
COMMENT ON COLUMN trading.asset.account_id IS '交易账户ID';
COMMENT ON COLUMN trading.asset.stock_code IS '股票或基金代码';
COMMENT ON COLUMN trading.asset.allocation IS '资产配置类型';
COMMENT ON COLUMN trading.asset.number IS '持仓数量';

DROP TABLE IF EXISTS trading.price_action_group;
CREATE TABLE trading.price_action_group(
        uuid VARCHAR(36) PRIMARY KEY DEFAULT uuid_generate_v4(),
        account_id VARCHAR(36) REFERENCES trading.account(uuid) NOT NULL,
        stock_code VARCHAR(6) REFERENCES trading.stock(stock_code) NOT NULL,
        capitalized DECIMAL(9, 3) DEFAULT 0.00,
        fee DECIMAL(9, 3) DEFAULT 0.00,
        profit DECIMAL(9, 3) DEFAULT 0.00,
        positions INTEGER DEFAULT 0,
        position_direction VARCHAR(50) REFERENCES trading.dictionary(code) NOT NULL,
        is_finish BOOLEAN DEFAULT FALSE,
        last_datetime TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NOW(),
        insert_time TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NOW()
);
COMMENT ON TABLE trading.price_action_group IS '价格行为操作组记录表';
COMMENT ON COLUMN trading.price_action_group.uuid IS '主键';
COMMENT ON COLUMN trading.price_action_group.account_id IS '交易账户ID';
COMMENT ON COLUMN trading.price_action_group.stock_code IS '股票或基金代码';
COMMENT ON COLUMN trading.price_action_group.capitalized IS '组持仓成本';
COMMENT ON COLUMN trading.price_action_group.fee IS '组交易费用';
COMMENT ON COLUMN trading.price_action_group.profit IS '组交易利润';
COMMENT ON COLUMN trading.price_action_group.positions IS '组持仓数';
COMMENT ON COLUMN trading.price_action_group.position_direction IS '头寸方向';
COMMENT ON COLUMN trading.price_action_group.is_finish IS '本组是否交易完成';
COMMENT ON COLUMN trading.price_action_group.last_datetime IS '最后交易时间日期';
COMMENT ON COLUMN trading.price_action_group.insert_time IS '数据登记时间日期';

DROP TABLE IF EXISTS trading.price_action;
CREATE TABLE trading.price_action(
        uuid VARCHAR(36) PRIMARY KEY DEFAULT uuid_generate_v4(),
        account_id VARCHAR(36) REFERENCES trading.account(uuid) NOT NULL,
        stock_code VARCHAR(6) REFERENCES trading.stock(stock_code)  NOT NULL,
        group_id VARCHAR(36) REFERENCES trading.price_action_group(uuid),
        operate VARCHAR(50) REFERENCES trading.dictionary(code) NOT NULL,
        method VARCHAR(50) REFERENCES trading.dictionary(code) NOT NULL,
        holdings VARCHAR(50) REFERENCES trading.dictionary(code) NOT NULL,
        reasons VARCHAR(50) REFERENCES trading.dictionary(code) NOT NULL,
        price DECIMAL(9, 3) NOT NULL,
        cost_price DECIMAL(9, 3) NOT NULL,
        stop_losses DECIMAL(9, 3) NOT NULL,
        risk_taking DECIMAL(9, 3) NOT NULL,
        number INTEGER,
        fee DECIMAL(9, 2) DEFAULT 0.00,
        message VARCHAR(500) DEFAULT NULL,
        datetime TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL,
        insert_time TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NOW()
);
COMMENT ON TABLE trading.price_action IS '价格行为操作记录表';
COMMENT ON COLUMN trading.price_action.uuid IS '主键';
COMMENT ON COLUMN trading.price_action.account_id IS '交易账户ID';
COMMENT ON COLUMN trading.price_action.stock_code IS '股票或基金代码';
COMMENT ON COLUMN trading.price_action.operate IS '交易类型：买入、卖出。 FOR 【stock.trade.type】';
COMMENT ON COLUMN trading.price_action.method IS '交易方式：master_t、slave_t FOR 【stock.trade.method】';
COMMENT ON COLUMN trading.price_action.holdings IS '仓位交易模式 FOR 【stock.trade.holdings】';
COMMENT ON COLUMN trading.price_action.reasons IS '交易理由 FOR 【stock.trade.reasons】';
COMMENT ON COLUMN trading.price_action.price IS '交易单价';
COMMENT ON COLUMN trading.price_action.cost_price IS '交易成本价';
COMMENT ON COLUMN trading.price_action.stop_losses IS '初始止损';
COMMENT ON COLUMN trading.price_action.risk_taking IS '风险金额';
COMMENT ON COLUMN trading.price_action.number IS '交易数量';
COMMENT ON COLUMN trading.price_action.fee IS '交易费用';
COMMENT ON COLUMN trading.price_action.message IS '交易说明';
COMMENT ON COLUMN trading.price_action.datetime IS '交易时间日期';
COMMENT ON COLUMN trading.price_action.insert_time IS '数据登记时间日期';

DROP TABLE IF EXISTS trading.transaction;
CREATE TABLE trading.transaction(
        uuid VARCHAR(36) PRIMARY KEY DEFAULT uuid_generate_v4(),
        account_id VARCHAR(36) REFERENCES trading.account(uuid) NOT NULL,
        stock_code VARCHAR(6) REFERENCES trading.stock(stock_code) DEFAULT NULL,
        stock_label VARCHAR(100) DEFAULT NULL,
        stock_market VARCHAR(50) DEFAULT NULL,
        stock_securities VARCHAR(50) DEFAULT NULL,
        operate VARCHAR(50) REFERENCES trading.dictionary(code) NOT NULL,
        allocation VARCHAR(50) REFERENCES trading.dictionary(code) DEFAULT NULL,
        price DECIMAL(9, 3) DEFAULT 0.00,
        stop_losses DECIMAL(9, 3) DEFAULT 0.00,
        number INTEGER DEFAULT 0,
        amount DECIMAL(9, 2) DEFAULT 0.00,
        balance DECIMAL(9, 2) DEFAULT 0.00,
        capitalized DECIMAL(9, 3) DEFAULT 0.00,
        reasons VARCHAR(50) REFERENCES trading.dictionary(code) DEFAULT NULL,
        positions INTEGER DEFAULT 0,
        commission DECIMAL(9, 2) DEFAULT 0.00,
        stamp_duty DECIMAL(9, 2) DEFAULT 0.00,
        transfer_fee DECIMAL(9, 2) DEFAULT 0.00,
        abstracts VARCHAR(500) DEFAULT NULL,
        datetime TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL,
        insert_time TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NOW()
);
COMMENT ON TABLE trading.transaction IS '证券及ETF操作记录表';
COMMENT ON COLUMN trading.transaction.uuid IS '主键';
COMMENT ON COLUMN trading.transaction.account_id IS '交易账户ID';
COMMENT ON COLUMN trading.transaction.stock_code IS '股票或基金代码';
COMMENT ON COLUMN trading.transaction.stock_label IS '股票或基金名称';
COMMENT ON COLUMN trading.transaction.stock_market IS '股票或基金所属市场';
COMMENT ON COLUMN trading.transaction.stock_securities IS '股票或基金所属类型';
COMMENT ON COLUMN trading.transaction.operate IS '交易类型：买入、卖出。 FOR 【stock.trade.type】';
COMMENT ON COLUMN trading.transaction.allocation IS '资产配置标签。 FOR 【asset.allocation】';
COMMENT ON COLUMN trading.transaction.price IS '交易单价';
COMMENT ON COLUMN trading.transaction.stop_losses IS '初始止损';
COMMENT ON COLUMN trading.transaction.number IS '交易数量';
COMMENT ON COLUMN trading.transaction.amount IS '交易金额';
COMMENT ON COLUMN trading.transaction.balance IS '当前账户余额';
COMMENT ON COLUMN trading.transaction.capitalized IS '当前持仓成本';
COMMENT ON COLUMN trading.transaction.reasons IS '交易原因';
COMMENT ON COLUMN trading.transaction.positions IS '当前持仓数';
COMMENT ON COLUMN trading.transaction.commission IS '交易佣金';
COMMENT ON COLUMN trading.transaction.stamp_duty IS '交易印花税';
COMMENT ON COLUMN trading.transaction.transfer_fee IS '交易过户费';
COMMENT ON COLUMN trading.transaction.abstracts IS '操作摘要';
COMMENT ON COLUMN trading.transaction.datetime IS '交易时间日期';
COMMENT ON COLUMN trading.transaction.insert_time IS '数据登记时间日期';

CREATE OR REPLACE FUNCTION trading.func_format_abstracts(JSON) RETURNS JSON
AS $$
DECLARE
    abstracts VARCHAR(500) DEFAULT NULL;
BEGIN
    IF($1->>'operate' IS NULL) THEN
        RETURN json_build_object('status', 'Error', 'msg', '参数【operate（交易类型）】不能为空！');
    ELSE
        IF ($1->>'operate' IN ('turnIn', 'turnOut', 'position', 'buy', 'apply', 'sell', 'stockDividend', 'shareAllotment') AND $1->>'stock_label' IS NULL) THEN
            RETURN json_build_object('status', 'Error', 'msg', '参数【stock_label（股票或基金名称）】不能为空！');
        END IF;
        IF ($1->>'operate' IN ('turnIn', 'turnOut', 'position', 'buy', 'apply', 'sell') AND $1->>'price' IS NULL) THEN
            RETURN json_build_object('status', 'Error', 'msg', '参数【price（交易单价）】不能为空！');
        END IF;
        IF ($1->>'operate' IN ('turnIn', 'turnOut', 'position', 'buy', 'apply', 'sell', 'shareAllotment') AND $1->>'number' IS NULL) THEN
            RETURN json_build_object('status', 'Error', 'msg', '参数【number（交易数量）】不能为空！');
        END IF;
        IF ($1->>'operate' IN ('turnIn', 'turnOut', 'position', 'buy', 'apply', 'sell', 'stockDividend', 'bondDividend', 'moneyDividend', 'toBank', 'toStock') AND $1->>'amount' IS NULL) THEN
            RETURN json_build_object('status', 'Error', 'msg', '参数【amount（发生金额）】不能为空！');
        END IF;
        IF ($1->>'operate' IN ('turnIn', 'turnOut', 'position', 'buy', 'apply', 'stockDividend', 'shareAllotment') AND $1->>'capitalized' IS NULL) THEN
            RETURN json_build_object('status', 'Error', 'msg', '参数【capitalized（成本价）】不能为空！');
        END IF;
        IF ($1->>'operate' IN ('turnIn', 'turnOut', 'position', 'buy', 'apply', 'stockDividend', 'shareAllotment') AND $1->>'positions' IS NULL) THEN
            RETURN json_build_object('status', 'Error', 'msg', '参数【position（持仓数量）】不能为空！');
        END IF;
        CASE $1->>'operate'
            WHEN 'turnIn' THEN
                abstracts := CONCAT('【转入】证券名称：', $1->>'stock_label', '，转入单价：￥', $1->>'price', '，转入数量：', $1->>'number', '，成本价：￥', $1->'capitalized', '，持仓数量：', $1->>'positions');
            WHEN 'turnOut' THEN
                abstracts := CONCAT('【转出】证券名称：', $1->>'stock_label', '，转出单价：￥', $1->>'price', '，转出数量：', $1->>'number', '，，成本价：￥', $1->'capitalized', '，持仓数量：', $1->>'positions');
            WHEN 'position' THEN
                abstracts := CONCAT('【调整持仓】证券名称：', $1->>'stock_label', '，调整持仓单价：￥', $1->>'price', '，调整持仓数量：', $1->>'number', '，现成本价：￥', $1->'capitalized', '，现持仓数量：', $1->>'positions');
            WHEN 'buy' THEN
                abstracts := CONCAT('【买入】证券名称：', $1->>'stock_label', '，买入单价：￥', $1->>'price', '，买入数量：', $1->>'number', '，现金余额减少：￥', $1->>'amount', '，成本价：￥', $1->'capitalized', '，持仓数量：', $1->>'positions');
            WHEN 'apply' THEN
                abstracts := CONCAT('【申购】证券名称：', $1->>'stock_label', '，申购单价：￥', $1->>'price', '，申购数量：', $1->>'number', '，现金余额减少：￥', $1->>'amount', '，成本价：￥', $1->'capitalized', '，持仓数量：', $1->>'positions');
            WHEN 'sell' THEN
                IF (CAST($1->>'positions' AS INTEGER) = 0 OR CAST($1->>'positions' AS INTEGER) IS NULL) THEN
                    abstracts := CONCAT('【清空】证券名称：', $1->>'stock_label', '，卖出单价：￥', $1->>'price', '，卖出数量：', $1->>'number', '，现金余额增加：￥', $1->>'amount');
                ELSE
                    abstracts := CONCAT('【卖出】证券名称：', $1->>'stock_label', '，卖出单价：￥', $1->>'price', '，卖出数量：', $1->>'number', '，现金余额增加：￥', $1->>'amount', '，成本价：￥', $1->'capitalized', '，持仓数量：', $1->>'positions');
                END IF;
            WHEN 'stockDividend' THEN
                abstracts := CONCAT('【股息】股票名称：', $1->>'stock_label', '，派息金额：￥', $1->>'amount', '，成本价：￥', $1->'capitalized', '，持仓数量：', $1->>'positions');
            WHEN 'shareAllotment' THEN
                abstracts := CONCAT('【派股】股票名称：', $1->>'stock_label', '，派股数量：', $1->>'number', '，成本价：￥', $1->'capitalized', '，持仓数量：', $1->>'positions');
            WHEN 'bondDividend' THEN
                abstracts := CONCAT('【国债收益】发生金额：￥', $1->>'amount');
            WHEN 'toBank' THEN
                abstracts := CONCAT('【证转银】发生金额：￥', $1->>'amount');
            WHEN 'toStock' THEN
                abstracts := CONCAT('【银转证】发生金额：￥', $1->>'amount');
            ELSE
                abstracts := CONCAT('【利息】发生金额：￥', $1->>'amount');
        END CASE;
    END IF;
    RETURN json_build_object('status', 'Success', 'msg', abstracts);
END
$$ LANGUAGE plpgsql;
COMMENT ON FUNCTION trading.func_format_abstracts(JSON) IS '格式化操作日志函数';

/**
  SELECT trading.func_calculate_fee(json_build_object('account_id', trading.func_get_default_account('GadflyBSD', 'account.stock')->'data'->>'uuid', 'operate', 'sell', 'stock_code', '600031', 'price', 21.68, 'number', 3000));
 */
CREATE OR REPLACE FUNCTION trading.func_calculate_fee(JSON) RETURNS JSON
AS $$
DECLARE
    securities VARCHAR DEFAULT NULL;
    accountJson JSON DEFAULT NULL;
    amount DECIMAL(9, 2) DEFAULT 0.00;
    capitalized DECIMAL(9, 3) DEFAULT 0.000;
    commission DECIMAL(9, 2) DEFAULT 0.00;
    stamp_duty DECIMAL(9, 2) DEFAULT 0.00;
    transfer_fee DECIMAL(9, 2) DEFAULT 0.00;
    total_fee DECIMAL(9, 2) DEFAULT 0.00;
BEGIN
    IF($1->>'operate' IS NULL) THEN
        RETURN json_build_object('status', 'Error', 'msg', '第一个参数中的【operate】不能为空！');
    END IF;
    IF($1->>'account_id' IS NULL) THEN
        RETURN json_build_object('status', 'Error', 'msg', '第一个参数中的【account_id】不能为空！');
    END IF;
    IF($1->>'stock_code' IS NULL) THEN
        RETURN json_build_object('status', 'Error', 'msg', '第一个参数中的【stock_code】不能为空！');
    END IF;
    IF($1->>'number' IS NULL) THEN
        RETURN json_build_object('status', 'Error', 'msg', '第一个参数中的【number】不能为空！');
    END IF;
    IF($1->>'price' IS NULL) THEN
        RETURN json_build_object('status', 'Error', 'msg', '第一个参数中的【price】不能为空！');
    END IF;
    IF($1->>'operate' IN ('buy', 'sell')) THEN
        amount := CAST($1->>'number' AS INTEGER) * CAST($1->>'price' AS DECIMAL);
        SELECT stock_securities into securities FROM trading.stock WHERE stock_code = $1->>'stock_code';
        SELECT row_to_json(acc) INTO accountJson FROM trading.account acc WHERE acc.uuid = $1->>'account_id';
        IF(securities IN ('etf.sh.fund', 'etf.sz.fund', 'lof.sz.fund', 'lof.sh.fund')) THEN
            -- 计算场内ETF交易佣金
            IF(accountJson->>'fund_rates' IS NULL) THEN
                RETURN json_build_object('status', 'Error', 'msg', '第二个参数中的场内基金佣金费率【fund_rates】不能为空！');
            END IF;
            IF(amount < 10000.00) THEN
                -- 是否小于10000
                IF(accountJson->>'fund_is_free5')THEN
                    -- 佣金免5
                    commission := amount * CAST(accountJson->>'fund_rates' AS DECIMAL);
                ELSE
                    commission := 5;
                END IF;
            ELSE
                commission := amount * CAST(accountJson->>'fund_rates' AS DECIMAL);
            END IF;
            stamp_duty := 0;
            transfer_fee := 0;
        ELSEIF(securities IN ('a.sh.stock', 'a.sz.stock', 'kcb.sh.stock', 'gem.sz.stock', 'a.bj.stock')) THEN
            -- 计算股票交易佣金
            IF(accountJson->>'stock_rates' IS NULL) THEN
                RETURN json_build_object('status', 'Error', 'msg', '第二个参数中的场内股票佣金费率【stock_rates】不能为空！');
            END IF;
            IF(amount * CAST(accountJson->>'stock_rates' AS DECIMAL) < 5) THEN
                -- 场内股票交易手续费是否小于5元
                IF(accountJson->>'stock_is_free5')THEN
                    -- 计算免5
                    commission := amount * CAST(accountJson->>'stock_rates' AS DECIMAL);
                ELSE
                    commission := 5;
                END IF;
            ELSE
                commission := amount * CAST(accountJson->>'stock_rates' AS DECIMAL);
            END IF;
            -- 计算股票印花税
            IF(accountJson->>'stamp_duty_rates' IS NULL) THEN
                RETURN json_build_object('status', 'Error', 'msg', '第二个参数中的证券印花税费率【stamp_duty_rates】不能为空！');
            END IF;
            IF($1->>'operate'= 'buy') THEN
                stamp_duty := 0;
            ELSE
                stamp_duty := amount * CAST(accountJson->>'stamp_duty_rates' AS DECIMAL);
            END IF;
            -- 计算上证过户费
            IF(accountJson->>'transfer_fee_rates' IS NULL) THEN
                RETURN json_build_object('status', 'Error', 'msg', '第二个参数中的上证过户费费率【transfer_fee_rates】不能为空！');
            END IF;
            IF(securities IN ('a.sh.stock', 'kcb.sh.stock')) THEN
                transfer_fee := amount * CAST(accountJson->>'transfer_fee_rates' AS DECIMAL);
            ELSE
                transfer_fee := 0;
            END IF;
        ELSE
            commission := 0;
            stamp_duty := 0;
            transfer_fee := 0;
        END IF;
    ELSE
        commission := 0;
        stamp_duty := 0;
        transfer_fee := 0;
    END IF;
    total_fee := commission + stamp_duty + transfer_fee;
    IF($1->>'operate' = 'buy') THEN
        capitalized = (amount + total_fee) / CAST($1->>'number' AS INTEGER);
    ELSE
        capitalized = (amount + total_fee) / CAST($1->>'number' AS INTEGER);
    END IF;
    RETURN json_build_object('status', 'Success', 'stock_code', $1->>'stock_code', 'securities', securities, 'operate', $1->>'operate', 'price', CAST($1->>'price' AS DECIMAL),
        'number', CAST($1->>'number' AS INTEGER), 'amount', amount, 'capitalized', capitalized, 'commission', commission, 'stamp_duty', stamp_duty, 'transfer_fee', transfer_fee, 'total_fee', total_fee);
END
$$ LANGUAGE plpgsql;
COMMENT ON FUNCTION trading.func_calculate_fee(JSON) IS '计算佣金、印花税和过户费的函数';

/**
  处理价格行为交易数据的函数
SELECT trading.func_price_action(json_build_object('operate', 'buy', 'account_id', trading.func_get_default_account('GadflyBSD', 'account.stock')->'data'->>'uuid',
        'stock_code', '600031', 'price', 21.56, 'number', 1500, 'fee', 42.67, 'stop_losses', 20.82, 'reasons', 'pa_buy_03', 'datetime', '2021-12-14 10:32'));
SELECT trading.func_price_action(json_build_object('operate', 'buy', 'account_id', trading.func_get_default_account('GadflyBSD', 'account.stock')->'data'->>'uuid',
        'stock_code', '600031', 'price', 22.21, 'number', 1500, 'fee', 43.67, 'stop_losses', 21.18, 'reasons', 'pa_buy_03', 'datetime', '2021-12-14 10:32'));
SELECT trading.func_price_action(json_build_object('operate', 'sell', 'account_id', trading.func_get_default_account('GadflyBSD', 'account.stock')->'data'->>'uuid',
        'stock_code', '600031', 'price', 23.52, 'number', 1000, 'fee', 48.67, 'stop_losses', 23.89, 'reasons', 'pa_sell_04', 'datetime', '2021-12-14 10:32'));
SELECT trading.func_price_action(json_build_object('operate', 'sell', 'account_id', trading.func_get_default_account('GadflyBSD', 'account.stock')->'data'->>'uuid',
        'stock_code', '600031', 'price', 23.92, 'number', 2000, 'fee', 50.16, 'stop_losses', 24.50, 'reasons', 'pa_sell_05', 'datetime', '2021-12-14 10:32'));
*/
CREATE OR REPLACE FUNCTION trading.func_price_action(JSON)
    RETURNS JSON AS $$
DECLARE
    pgrn_MESSAGE TEXT;
    pgrn_DETAIL TEXT;
    pgrn_HINT TEXT;
    pgrn_CONTEXT TEXT;
    pgrn_SQLSTATE TEXT;
    operateP VARCHAR DEFAULT $1->>'operate';                                -- 传参：交易方式
    stockCodeP VARCHAR DEFAULT $1->>'stock_code';                           -- 传参：证券代码
    accountIdP VARCHAR DEFAULT $1->>'account_id';                           -- 传参：账户UUID
    reasonsP VARCHAR DEFAULT $1->>'reasons';                                -- 传参：交易理由
    priceP DECIMAL(9, 2) DEFAULT CAST($1->>'price' AS DECIMAL);             -- 传参，当前交易单价
    numberP INTEGER DEFAULT CAST($1->>'number' AS INTEGER);                 -- 传参，当前交易数量
    feeP DECIMAL(9, 2) DEFAULT CAST($1->>'fee' AS DECIMAL);                 -- 传参，当前交易费用
    stopLossesP DECIMAL(9, 2) DEFAULT CAST($1->>'stop_losses' AS DECIMAL);  -- 传参，当前交易止损
    datetimeP TIMESTAMP DEFAULT CAST($1->>'datetime' AS TIMESTAMP);         -- 传参，当前交易时间
    groupIdV VARCHAR(36) DEFAULT NULL;          -- 价格行为组ID
    amountV DECIMAL(9, 2) DEFAULT 0.00;         -- 交易金额
    costPriceV DECIMAL(9, 2) DEFAULT 0.00;      -- 交易成本价
    riskTakingV  DECIMAL(9, 2) DEFAULT 0.00;    -- 盈亏平衡价位，即一倍风险回报
    reasonsV VARCHAR DEFAULT NULL;              -- 交易理由
    messageV VARCHAR DEFAULT NULL;              -- 构造计算结果说明
    numberV INTEGER DEFAULT 0;
    feeV DECIMAL(9, 2) DEFAULT 0.00;
    selectJson JSON DEFAULT NULL;
    paGroupUuid VARCHAR(36) DEFAULT NULL;
    groupIsFinish BOOLEAN DEFAULT FALSE;
BEGIN
    IF(operateP IS NULL) THEN
        RAISE EXCEPTION 'operate is null（交易方式为空）' USING HINT = $1, DETAIL = 'JSON参数中的【operate】KEY不能为空';
    END IF;
    IF(stockCodeP IS NULL) THEN
        RAISE EXCEPTION 'stock_code is null（证券代码为空）' USING HINT  = $1, DETAIL = 'JSON参数中的【stock_code】KEY不能为空';
    END IF;
    IF(accountIdP IS NULL) THEN
        RAISE EXCEPTION 'account_id is null（交易账号ID为空）' USING HINT  = $1, DETAIL = 'JSON参数中的【account_id】KEY不能为空';
    END IF;
    IF(priceP IS NULL) THEN
        RAISE EXCEPTION 'price is null（交易单价为空）' USING HINT  = $1, DETAIL = 'JSON参数中的【price】KEY不能为空';
    END IF;
    IF(numberP IS NULL) THEN
        RAISE EXCEPTION 'number is null（交易数量为空）' USING HINT  = $1, DETAIL = 'JSON参数中的【number】KEY不能为空';
    END IF;
    IF(feeP IS NULL) THEN
        RAISE EXCEPTION 'fee is null（交易费用为空）' USING HINT  = $1, DETAIL = 'JSON参数中的【fee】KEY不能为空';
    END IF;
    IF(stopLossesP IS NULL) THEN
        RAISE EXCEPTION 'stop_losses is null（止损价为空）' USING HINT  = $1, DETAIL = 'JSON参数中的【stop_losses】KEY不能为空';
    END IF;
    IF(reasonsP IS NULL) THEN
        RAISE EXCEPTION 'reasons is null（交易理由为空）' USING HINT  = $1, DETAIL = 'JSON参数中的【reasons】KEY不能为空';
    END IF;
    IF(datetimeP IS NULL) THEN
        RAISE EXCEPTION 'datetime is null（交易时间为空）' USING HINT  = $1, DETAIL = 'JSON参数中的【datetime】KEY不能为空';
    END IF;
    WITH position_price_action_group AS (
        SELECT p.stock_code, p.account_id, p.capitalized, p.number, gl.uuid AS gl_uuid, gs.uuid AS gs_uuid, coalesce(gl.positions, 0) AS gl_positions, coalesce(gs.positions, 0) AS gs_positions FROM trading.position p
            LEFT JOIN trading.price_action_group gl ON p.stock_code = gl.stock_code AND p.account_id = gl.account_id AND gl.position_direction = 'long.positions' AND gl.is_finish IS FALSE AND gl.positions !=0
            LEFT JOIN trading.price_action_group gs ON p.stock_code = gs.stock_code AND p.account_id = gs.account_id AND gs.position_direction = 'short.positions' AND gs.is_finish IS FALSE AND gs.positions !=0
        ) SELECT row_to_json(t) INTO selectJson FROM position_price_action_group t WHERE t.account_id = accountIdP AND t.stock_code = stockCodeP;
    SELECT label INTO reasonsV FROM trading.dictionary WHERE code = reasonsP;
    IF (operateP = 'buy') THEN
        -- 买入操作
        amountV := priceP * numberP + feeP;
        costPriceV := amountV/numberP;
        riskTakingV := ABS(costPriceV - stopLossesP);
        IF (CAST(selectJson->>'number' AS INTEGER) != 0) THEN
            IF (CAST(selectJson->>'gl_positions' AS INTEGER) != 0) THEN
                groupIdV := selectJson->>'gl_uuid';
                messageV := '总持仓量非空（' || CAST(selectJson->>'number' AS VARCHAR) || '股），主多仓非空（' || CAST(selectJson->>'gl_positions' AS VARCHAR) ||'股），买入操作：主多仓加仓，交易理由：' || reasonsV;
                INSERT INTO trading.price_action(account_id, stock_code, group_id, operate, method, holdings, reasons, price, cost_price, stop_losses, risk_taking, number, fee, message, datetime)
                VALUES (accountIdP, stockCodeP, groupIdV, operateP, 'slave_t', 'add.holdings', reasonsP, priceP, costPriceV, stopLossesP, riskTakingV, numberP, feeP, messageV, datetimeP);
                UPDATE trading.price_action_group SET capitalized = (priceP * numberP + capitalized * positions + feeP)/(positions + numberP), fee = fee + feeP, positions = positions + numberP, last_datetime = datetimeP WHERE uuid = groupIdV;
            ELSEIF (CAST(selectJson->>'gs_positions' AS INTEGER) != 0) THEN
                -- 空仓持仓量非空，总持仓非空
                groupIdV := selectJson->>'gs_uuid';
                IF (CAST(selectJson->>'gs_positions' AS INTEGER) > numberP) THEN
                    numberV := numberP;
                    messageV := '总持仓非空（' || CAST(selectJson->>'number' AS VARCHAR) || '股），空仓持仓量（' || CAST(selectJson->>'gs_positions' AS VARCHAR) || '股）大于买入交易量，买入操作：空仓减仓，交易理由：' || reasonsV;
                    INSERT INTO trading.price_action(account_id, stock_code, group_id, operate, method, holdings, reasons, price, cost_price, stop_losses, risk_taking, number, fee, message, datetime)
                    VALUES (accountIdP, stockCodeP, groupIdV, operateP, 'slave_t', 'minus.holdings', reasonsP, priceP, costPriceV, stopLossesP, riskTakingV, numberP, feeP, messageV, datetimeP);
                ELSEIF (CAST(selectJson->>'gs_positions' AS INTEGER) = numberP) THEN
                    numberV := numberP;
                    groupIsFinish = True;
                    messageV := '总持仓非空（' || CAST(selectJson->>'number' AS VARCHAR) || '股），空仓持仓量（' || CAST(selectJson->>'gs_positions' AS VARCHAR) || '股）等于买入交易量，买入操作：空仓平仓，交易理由：' || reasonsV;
                    INSERT INTO trading.price_action(account_id, stock_code, group_id, operate, method, holdings, reasons, price, cost_price, stop_losses, risk_taking, number, fee, message, datetime)
                    VALUES (accountIdP, stockCodeP, groupIdV, operateP, 'slave_t', 'close.holdings', reasonsP, priceP, costPriceV, stopLossesP, riskTakingV, numberP, feeP, messageV, datetimeP);
                ELSE
                    numberV := CAST(selectJson->>'gs_positions' AS INTEGER);
                    feeV := feeP / numberP * numberV;
                    groupIsFinish := True;
                    messageV := '总持仓非空（' || CAST(selectJson->>'number' AS VARCHAR) || '股），空仓持仓量（' || CAST(selectJson->>'gs_positions' AS VARCHAR) || '股）小于买入交易量，买入操作：主空仓先进行平仓后再新建多仓，交易理由：' || reasonsV;
                    INSERT INTO trading.price_action(account_id, stock_code, group_id, operate, method, holdings, reasons, price, cost_price, stop_losses, risk_taking, number, fee, message, datetime)
                    VALUES (accountIdP, stockCodeP, groupIdV, operateP, 'slave_t', 'close.holdings', reasonsP, priceP, costPriceV, stopLossesP, riskTakingV, numberV, feeV, messageV, datetimeP);
                    amountV := amountV / numberP * numberV;
                    feeV := feeP / numberP * (numberP - numberV);
                    messageV := '总持仓非空（' || CAST(selectJson->>'number' AS VARCHAR)  || '股），空仓持仓量（' || CAST(selectJson->>'gs_positions' AS VARCHAR) || '股）小于买入交易量，买入操作：原空仓已平仓现将剩余交易量（' || CAST((numberP - numberV) AS VARCHAR) || '股）进行新建多仓，交易理由：' || reasonsV;
                    INSERT INTO trading.price_action_group(account_id, stock_code, capitalized, fee, positions, position_direction)
                    VALUES (accountIdP, stockCodeP, amountV/numberV , feeV, numberP - numberV, 'long.positions') RETURNING uuid INTO paGroupUuid;
                    INSERT INTO trading.price_action(account_id, stock_code, group_id, operate, method, holdings, reasons, price, cost_price, stop_losses, risk_taking, number, fee, message, datetime)
                    VALUES (accountIdP, stockCodeP, paGroupUuid, operateP, 'master_t', 'open.holdings', reasonsP, priceP, costPriceV, stopLossesP, riskTakingV, numberP - numberV, feeV, messageV, datetimeP);
                END IF;
                UPDATE trading.price_action_group
                SET capitalized = CASE WHEN groupIsFinish = True THEN 0 ELSE (capitalized * positions - priceP * numberP + feeP)/(positions - numberP) END,
                    fee = CASE WHEN groupIsFinish = True THEN fee + feeP / numberP * numberV ELSE fee + feeP END, profit = profit + ((priceP - capitalized) * numberV - feeP / numberP * numberV),
                    positions = positions - numberP, is_finish = groupIsFinish, last_datetime = datetimeP WHERE uuid = groupIdV;
            ELSE
                messageV := '总持仓量（' || CAST(selectJson->>'number' AS VARCHAR)  || '股）非空，多仓、空仓持仓量均为空，买入操作：新建主多仓，交易理由：' || reasonsV;
                INSERT INTO trading.price_action_group(account_id, stock_code, capitalized, fee, positions, position_direction)
                VALUES (accountIdP, stockCodeP, amountV/numberP , feeP, numberP, 'long.positions') RETURNING uuid INTO paGroupUuid;
                INSERT INTO trading.price_action(account_id, stock_code, group_id, operate, method, holdings, reasons, price, cost_price, stop_losses, risk_taking, number, fee, message, datetime)
                VALUES (accountIdP, stockCodeP, paGroupUuid, operateP, 'master_t', 'open.holdings', reasonsP, priceP, costPriceV, stopLossesP, riskTakingV, numberP, feeP, messageV, datetimeP);
            END IF;
        ELSE
            messageV := '总持仓量为空，同时多仓持仓量为空，买入操作：新建主多仓，交易理由：' || reasonsV;
            INSERT INTO trading.price_action_group(account_id, stock_code, capitalized, fee, positions, position_direction)
            VALUES (accountIdP, stockCodeP, amountV/numberP , feeP, numberP, 'long.positions') RETURNING uuid INTO paGroupUuid;
            INSERT INTO trading.price_action(account_id, stock_code, group_id, operate, method, holdings, reasons, price, cost_price, stop_losses, risk_taking, number, fee, message, datetime)
            VALUES (accountIdP, stockCodeP, paGroupUuid, operateP, 'master_t', 'open.holdings', reasonsP, priceP, costPriceV, stopLossesP, riskTakingV, numberP, feeP, messageV, datetimeP);
        END IF;
    ELSEIF (operateP = 'sell') THEN
        -- 卖出操作
        amountV := priceP * numberP - feeP;
        costPriceV := amountV/numberP;
        riskTakingV := ABS(costPriceV - stopLossesP);
        IF (CAST(selectJson->>'number' AS INTEGER) != 0) THEN
            IF (CAST(selectJson->>'gl_positions' AS INTEGER) != 0) THEN
                -- 多仓记录非空，总持仓非空
                groupIdV := selectJson->>'gl_uuid';
                IF (CAST(selectJson->>'number' AS INTEGER) = CAST(selectJson->>'gl_positions' AS INTEGER)) THEN
                    -- 总持仓量等于多仓组持仓量
                    IF (CAST(selectJson->>'gl_positions' AS INTEGER) > numberP) THEN
                        messageV := '总持仓量（' || CAST(selectJson->>'number' AS VARCHAR) || '股）等于主多仓持仓量（' || CAST(selectJson->>'gl_positions' AS VARCHAR) || '股），主多仓持仓量大于交易量，卖出操作：主多仓减仓，交易理由：' || reasonsV;
                        INSERT INTO trading.price_action(account_id, stock_code, group_id, operate, method, holdings, reasons, price, cost_price, stop_losses, risk_taking, number, fee, message, datetime)
                        VALUES (accountIdP, stockCodeP, groupIdV, operateP, 'slave_t', 'minus.holdings', reasonsP, priceP, costPriceV, stopLossesP, riskTakingV, numberP, feeP, messageV, datetimeP);
                    ELSEIF (CAST(selectJson->>'gl_positions' AS INTEGER) = numberP) THEN
                        groupIsFinish = TRUE;
                        messageV := '总持仓量（' || CAST(selectJson->>'number' AS VARCHAR) || '股）等于主多仓持仓量（' || CAST(selectJson->>'gl_positions' AS VARCHAR) || '股），主多仓持仓量等于交易量，卖出操作：主多仓清仓，交易理由：' || reasonsV;
                        INSERT INTO trading.price_action(account_id, stock_code, group_id, operate, method, holdings, reasons, price, cost_price, stop_losses, risk_taking, number, fee, message, datetime)
                        VALUES (accountIdP, stockCodeP, groupIdV, operateP, 'slave_t', 'clear.holdings', reasonsP, priceP, costPriceV, stopLossesP, riskTakingV, numberP, feeP, messageV, datetimeP);
                    ELSE
                        RAISE EXCEPTION '价格行为交易处理数据失败！' USING HINT = '总持仓量：' || CAST(selectJson->>'number' AS VARCHAR) || '股，多仓组持仓量：' || CAST(selectJson->>'gl_positions' AS VARCHAR) || '股', DETAIL = '总持仓量、主多仓持仓量均小于交易量！';
                    END IF;
                    UPDATE trading.price_action_group SET capitalized = (capitalized * positions - priceP * numberP - feeP)/(positions - numberP), fee = fee + feeP, is_finish = groupIsFinish,
                                                          profit = profit + ((priceP - capitalized) * numberP - feeP), positions = positions - numberP, last_datetime = datetimeP WHERE uuid = groupIdV;
                ELSEIF (CAST(selectJson->>'number' AS INTEGER) > CAST(selectJson->>'gl_positions' AS INTEGER)) THEN
                    ---- 总持仓量大于多仓组持仓量
                    IF (CAST(selectJson->>'gl_positions' AS INTEGER) > numberP) THEN
                        numberV := numberP;
                        messageV := '总持仓量（' || CAST(selectJson->>'number' AS VARCHAR) || '股）大于主多仓持仓量（' || CAST(selectJson->>'gl_positions' AS VARCHAR) || '股），主多仓持仓量大于交易量，卖出操作：主多仓减仓，交易理由：' || reasonsV;
                        INSERT INTO trading.price_action(account_id, stock_code, group_id, operate, method, holdings, reasons, price, cost_price, stop_losses, risk_taking, number, fee, message, datetime)
                        VALUES (accountIdP, stockCodeP, groupIdV, operateP, 'slave_t', 'minus.holdings', reasonsP, priceP, costPriceV, stopLossesP, riskTakingV, numberP, feeP, messageV, datetimeP);
                    ELSEIF (CAST(selectJson->>'gl_positions' AS INTEGER) = numberP) THEN
                        numberV := numberP;
                        groupIsFinish = TRUE;
                        messageV := '总持仓量（' || CAST(selectJson->>'number' AS VARCHAR) || '股）大于主多仓持仓量（' || CAST(selectJson->>'gl_positions' AS VARCHAR) || '股），主多仓持仓量等于交易量，卖出操作：主多仓平仓，交易理由：' || reasonsV;
                        INSERT INTO trading.price_action(account_id, stock_code, group_id, operate, method, holdings, reasons, price, cost_price, stop_losses, risk_taking, number, fee, message, datetime)
                        VALUES (accountIdP, stockCodeP, groupIdV, operateP, 'slave_t', 'close.holdings', reasonsP, priceP, costPriceV, stopLossesP, riskTakingV, numberP, feeP, messageV, datetimeP);
                    ELSE
                        numberV := CAST(selectJson->>'gl_positions' AS INTEGER);
                        groupIsFinish = TRUE;
                        amountV := amountV/numberP * numberV;
                        messageV := '总持仓量（' || CAST(selectJson->>'number' AS VARCHAR) || '股）大于主多仓持仓量（' || CAST(selectJson->>'gl_positions' AS VARCHAR) || '股），交易量大于主多仓持仓量，卖出操作：主多仓先进行平仓后再新建主空仓，交易理由：' || reasonsV;
                        INSERT INTO trading.price_action(account_id, stock_code, group_id, operate, method, holdings, reasons, price, cost_price, stop_losses, risk_taking, number, fee, message, datetime)
                        VALUES (accountIdP, stockCodeP, groupIdV, operateP, 'slave_t', 'close.holdings', reasonsP, priceP, costPriceV, stopLossesP,
                                riskTakingV, CAST(selectJson->>'gl_positions' AS INTEGER), feeP / numberP * CAST(selectJson->>'gl_positions' AS INTEGER), messageV, datetimeP);
                        amountV := amountV/numberP * (numberP - numberV);
                        feeV := feeP / numberP * numberV;
                        messageV := '总持仓量（' || CAST(selectJson->>'number' AS VARCHAR) || '股）大于主多仓持仓量（' || CAST(selectJson->>'gl_positions' AS VARCHAR) || '股），交易量大于主多仓持仓量，卖出操作：原主多仓已平仓，现现将剩余交易量（' || CAST((numberP - numberV) AS VARCHAR) || '股）进行新建主空仓，交易理由：' || reasonsV;
                        INSERT INTO trading.price_action_group(account_id, stock_code, capitalized, fee, positions, position_direction)
                        VALUES (accountIdP, stockCodeP, amountV/(numberP - numberV), feeV, (numberP - numberV), 'short.positions') RETURNING uuid INTO paGroupUuid;
                        INSERT INTO trading.price_action(account_id, stock_code, group_id, operate, method, holdings, reasons, price, cost_price, stop_losses, risk_taking, number, fee, message, datetime)
                        VALUES (accountIdP, stockCodeP, paGroupUuid, operateP, 'master_t', 'open.holdings', reasonsP, priceP, costPriceV, stopLossesP, riskTakingV, (numberP - numberV), feeV, messageV, datetimeP);
                    END IF;
                ELSE
                    RAISE EXCEPTION '价格行为交易处理数据失败！' USING HINT = '总持仓量：' || CAST(selectJson->>'number' AS VARCHAR) || '股，多仓组持仓量：' || CAST(selectJson->>'gl_positions' AS VARCHAR) || '股', DETAIL = '总持仓量小于多仓组持仓量！';
                END IF;
                UPDATE trading.price_action_group SET capitalized = CASE WHEN groupIsFinish = TRUE THEN 0 ELSE (capitalized * positions - priceP * numberP - feeP)/(positions - numberP) END,
                                                      fee = fee + feeP, profit = profit + ((priceP - capitalized) * numberV - feeP), last_datetime = datetimeP,
                                                      positions = CASE WHEN groupIsFinish = TRUE THEN 0 ELSE positions - numberP END, is_finish = groupIsFinish WHERE uuid = groupIdV;
            ELSEIF (CAST(selectJson->>'gs_positions' AS INTEGER) != 0) THEN
                -- 空仓组持仓量非空，总持仓非空
                groupIdV := selectJson->>'gs_uuid';
                IF (selectJson->>'number' >= (selectJson->>'gs_positions' + numberP)) THEN
                    messageV := '总持仓量（' || CAST(selectJson->>'number' AS VARCHAR) || '股）大于等于空仓组持仓量（' || CAST(selectJson->>'gs_positions' AS VARCHAR) || '股）和卖出交易量之和：卖出操作主空仓加仓，交易理由：' || reasonsV;
                    INSERT INTO trading.price_action(account_id, stock_code, group_id, operate, method, holdings, reasons, price, cost_price, stop_losses, risk_taking, number, fee, message, datetime)
                    VALUES (accountIdP, stockCodeP, groupIdV, operateP, 'master_t', 'add.holdings', reasonsP, priceP, costPriceV, stopLossesP, riskTakingV, numberP, feeP, messageV, datetimeP);
                    UPDATE trading.price_action_group SET capitalized = (capitalized * positions - priceP * numberP - feeP)/(positions - numberP), fee = fee + feeP,
                                                          positions = positions - numberP, last_datetime = datetimeP WHERE uuid = groupIdV;
                ELSE
                    RAISE EXCEPTION '价格行为交易处理数据失败！' USING HINT = '总持仓量：' || CAST(selectJson->>'number' AS VARCHAR) || '股，空仓组持仓量：' || CAST(selectJson->>'gs_positions' AS VARCHAR) || '股', DETAIL = '总持仓量小于空仓组持仓量和卖出交易量之和！';
                END IF;
            ELSE
                messageV := '总持仓量（' || CAST(selectJson->>'number' AS VARCHAR) || '股）非空，多仓、空仓组持仓量均为空，卖出操作新建主空仓，交易理由：' || reasonsV;
                INSERT INTO trading.price_action_group(account_id, stock_code, capitalized, fee, positions, position_direction)
                VALUES (accountIdP, stockCodeP, amountV/numberP , feeP, numberP, 'short.positions') RETURNING uuid INTO paGroupUuid;
                INSERT INTO trading.price_action(account_id, stock_code, group_id, operate, method, holdings, reasons, price, cost_price, stop_losses, risk_taking, number, fee, message, datetime)
                VALUES (accountIdP, stockCodeP, paGroupUuid, operateP, 'master_t', 'open.holdings', reasonsP, priceP, costPriceV, stopLossesP, riskTakingV, numberP, feeP, messageV, datetimeP);
            END IF;
        ELSE
            RAISE EXCEPTION '价格行为交易处理数据失败！' USING HINT = CAST(selectJson AS VARCHAR), DETAIL = '总持仓记录为空不能卖空！';
        END IF;
    ELSE
        messageV := '【operate】参数（' || operateP || '）不适用此方法，不做任何操作！';
    END IF;
    RETURN json_build_object('status', 'Success', 'message', messageV, 'paGroupUuid', coalesce(paGroupUuid, selectJson->>'gl_uuid', selectJson->>'gs_uuid'));
    EXCEPTION WHEN OTHERS THEN
            GET STACKED DIAGNOSTICS pgrn_MESSAGE = MESSAGE_TEXT, pgrn_DETAIL = PG_EXCEPTION_DETAIL, pgrn_HINT = PG_EXCEPTION_HINT, pgrn_CONTEXT = PG_EXCEPTION_CONTEXT, pgrn_SQLSTATE = RETURNED_SQLSTATE;
            RAISE NOTICE 'MESSAGE: %', pgrn_MESSAGE;
            RAISE NOTICE 'DETAIL:　%', pgrn_DETAIL;
            RAISE NOTICE 'HINT:　%', pgrn_HINT;
            RAISE NOTICE 'CONTEXT:　%', pgrn_CONTEXT;
            RAISE NOTICE 'SQLSTATE:　%', pgrn_SQLSTATE;
END
$$ LANGUAGE plpgsql;
COMMENT ON FUNCTION trading.func_calculate_fee(JSON) IS '处理价格行为交易数据的函数';

CREATE OR REPLACE FUNCTION trading.trigger_insert_before_transaction_function()
  RETURNS TRIGGER AS $$
DECLARE
    pgrn_MESSAGE TEXT;
    pgrn_DETAIL TEXT;
    pgrn_HINT TEXT;
    pgrn_CONTEXT TEXT;
    pgrn_SQLSTATE TEXT;
    selectNum INTEGER DEFAULT 0;
    abstracts JSON DEFAULT NULL;
    inString VARCHAR(255) DEFAULT NULL;
    accountLabel VARCHAR(255) DEFAULT NULL;                                         -- 当前账户基本数据
    calculateFeeJson JSON;
    reasonsMsg VARCHAR(255) DEFAULT NULL;
BEGIN
    IF (NEW.operate IS NULL) THEN
        SELECT STRING_AGG(code, ', ') INTO inString FROM trading.dictionary WHERE kind = 'stock.trade.type';
        RAISE EXCEPTION '证券交易操作失败！' USING HINT = '必须指定交易类型！正确的交易类型是：' || inString, ERRCODE = '77240', DETAIL = 'operate = NULL（交易类型不能为空）';
    ELSEIF (NEW.operate NOT IN (SELECT code FROM trading.dictionary WHERE kind = 'stock.trade.type')) THEN
        SELECT STRING_AGG(code, ', ') INTO inString FROM trading.dictionary WHERE kind = 'stock.trade.type';
        RAISE EXCEPTION '证券交易操作失败！' USING HINT = '所指定的交易类型不在范围内！正确的交易类型是：' || inString, ERRCODE = '77243',
            DETAIL = '交易类型：' || NEW.operate || '（交易类型指定错误）';
    END IF;
    IF (NEW.operate IN ('position', 'turnIn', 'turnOut', 'buy', 'sell', 'apply', 'stockDividend', 'shareAllotment')) THEN
        IF (NEW.stock_code IS NOT NULL) THEN
            -- 获取证券信息
            IF ((SELECT count(1) FROM trading.stock WHERE stock_code = NEW.stock_code) <> 0) THEN
                SELECT stock_label, stock_market, stock_securities INTO NEW.stock_label, NEW.stock_market, NEW.stock_securities
                                                                   FROM trading.stock WHERE stock_code = NEW.stock_code;
            ELSE
                RAISE EXCEPTION '证券交易操作失败！' USING HINT = '所指定的证券代码不存在！', ERRCODE = '77247',
                    DETAIL = '证券代码：' || NEW.stock_code || '，数据库中不存在该代码，请检查！';
            END IF;
        ELSE
            RAISE EXCEPTION '证券交易操作失败！' USING HINT = '未指定证券代码！', ERRCODE = '77247',
                    DETAIL = 'stock_code = NULL（证券代码不能为空，请检查！）';
        END IF;
    END IF;
    IF(NEW.operate IN ('position', 'turnIn', 'turnOut', 'buy', 'sell', 'apply', 'shareAllotment')) THEN
        IF(NEW.number IS NULL OR NEW.number = 0) THEN
            RAISE EXCEPTION '证券交易操作失败！' USING HINT = '未指定证券的交易数量！', ERRCODE = '77245',
                DETAIL = '证券代码：' || NEW.stock_code || '，证券名称：' || NEW.stock_label || '，交易数量不能为空或为零';
        END IF;
    END IF;
    IF(NEW.operate IN ('position', 'turnIn', 'turnOut', 'buy', 'sell', 'apply')) THEN
        IF(NEW.price IS NULL OR NEW.price = 0) THEN
            RAISE EXCEPTION '证券交易操作失败！' USING HINT = '交易单价错误！', ERRCODE = '77245',
                DETAIL = '证券代码：' || NEW.stock_code || '，证券名称：' || NEW.stock_label || '，交易单价不能为空或为零';
        END IF;
    END IF;
    IF(NEW.operate IN ('stockDividend', 'bondDividend', 'moneyDividend', 'toStock', 'toBank')) THEN
        IF(NEW.amount IS NULL OR NEW.amount = 0) THEN
            RAISE EXCEPTION '证券交易操作失败！' USING HINT = '交易金额错误！', ERRCODE = '77245',
                DETAIL = '证券代码：' || NEW.stock_code || '，证券名称：' || NEW.stock_label || '，交易金额不能为空或为零）';
        END IF;
    END IF;
    IF (NEW.operate IN ('buy', 'sell')) THEN
        IF (NEW.allocation IS NULL) THEN
            RAISE EXCEPTION '统计资产配置组合失败！' USING HINT = '资产配置标签【allocation】不能为空！', ERRCODE = '77250';
        ELSEIF (NEW.allocation NOT IN (SELECT code FROM trading.dictionary WHERE kind = 'asset.allocation')) THEN
            SELECT STRING_AGG(code, ', ') INTO inString FROM trading.dictionary WHERE kind = 'asset.allocation';
            RAISE EXCEPTION '统计资产配置组合失败！' USING HINT = '资产配置标签【allocation】不在预设范围内！', ERRCODE = '77251',
                DETAIL = '持仓证券：' || NEW.stock_label || '，资产配置标签是：' || NEW.allocation || '，资产配置标签的范围是：' || inString;
        ELSEIF (NEW.allocation = 'price_action') THEN
            IF(NEW.stop_losses IS NULL OR NEW.stop_losses = 0) THEN
                RAISE EXCEPTION '证券交易操作失败！' USING HINT = '交易止损价格错误！', ERRCODE = '77245',
                    DETAIL = '证券代码：' || NEW.stock_code || '，证券名称：' || NEW.stock_label || '，止损价格不能为空或为零）';
            END IF;
            IF(NEW.reasons IS NULL) THEN
                RAISE EXCEPTION '证券交易操作失败！' USING HINT = '交易理由出现错误！', ERRCODE = '77245',
                    DETAIL = '证券代码：' || NEW.stock_code || '，证券名称：' || NEW.stock_label || '，交易理由不能为空）';
            ELSEIF (NEW.reasons NOT IN (SELECT code FROM trading.dictionary WHERE kind = NEW.operate || '.price.action')) THEN
                SELECT STRING_AGG(code, ', ') INTO inString FROM trading.dictionary WHERE kind = NEW.operate || '.price.action';
                RAISE EXCEPTION '价格行为交易失败！' USING HINT = '交易理由标签【reasons】不在预设范围内！', ERRCODE = '77251',
                    DETAIL = '持仓证券：' || NEW.stock_label || '，资产配置标签是：' || NEW.allocation || '，交易理由标签的范围是：' || inString;
            ELSE
            END IF;
        ELSE
        END IF;
    END IF;
    IF (NEW.datetime IS NULL) THEN
        RAISE EXCEPTION '证券交易操作失败！' USING HINT = '必须指定交易日期时间，交易日期时间不能为空！', ERRCODE = '77242', DETAIL = 'datetime = NULL（交易日期时间不能为空）';
    END IF;
    -- 获取当前账户数据
    IF (NEW.account_id IS NULL) THEN
        SELECT uuid, account_label INTO NEW.account_id, accountLabel FROM trading.account WHERE is_default IS TRUE AND account_type = 'account.stock';
    ELSE
        IF ((SELECT count(1) FROM trading.account WHERE uuid = NEW.account_id) = 0) THEN
            RAISE EXCEPTION '证券交易操作失败！' USING HINT = '所指定的账户不存在！', ERRCODE = '77244', DETAIL = 'account_id = ' || NEW.account_id || '（账户UUID指定错误）';
        ELSE
            SELECT account_label INTO accountLabel FROM trading.account WHERE uuid = NEW.account_id;
        END IF;
    END IF;
    -- 转入、买入、申购或卖出、派股、派息分红交易
    IF (NEW.operate IN ('position', 'turnIn', 'turnOut', 'buy', 'sell', 'apply', 'stockDividend', 'shareAllotment')) THEN
        IF (NEW.operate IN ('position', 'turnIn', 'turnOut', 'buy', 'sell', 'apply')) THEN
            -- 计算佣金、印花税和过户费
            SELECT trading.func_calculate_fee(json_build_object('account_id', NEW.account_id, 'stock_code', NEW.stock_code, 'operate', NEW.operate,'number', NEW.number, 'price', NEW.price)) INTO calculateFeeJson;
            IF(calculateFeeJson->>'status' = 'Success') THEN
                NEW.commission := CAST(calculateFeeJson->>'commission' AS DECIMAL);
                NEW.stamp_duty := CAST(calculateFeeJson->>'stamp_duty' AS DECIMAL);
                NEW.transfer_fee := CAST(calculateFeeJson->>'transfer_fee' AS DECIMAL);
            ELSE
                RAISE EXCEPTION '计算佣金、印花税和过户费失败！' USING HINT = calculateFeeJson->>'msg', ERRCODE = '77248', DETAIL = replace(SQLERRM, '"', '`');
            END IF;
            -- 获取原持仓数量和持仓成本
            SELECT capitalized, number INTO NEW.capitalized, NEW.positions FROM trading.position WHERE stock_code = NEW.stock_code AND account_id = NEW.account_id;
            IF (NEW.operate = 'buy') THEN
                NEW.amount := NEW.number * NEW.price + NEW.commission + NEW.stamp_duty + NEW.transfer_fee;
            ELSEIF (NEW.operate = 'sell') THEN
                NEW.amount := NEW.number * NEW.price - NEW.commission - NEW.stamp_duty - NEW.transfer_fee;
            ELSEIF (NEW.operate IN ('position', 'turnIn', 'turnOut', 'apply')) THEN
                NEW.amount := NEW.number * NEW.price;
            ELSE
            END IF;
            -- 统计操作所属资产配置组合的数据
            SELECT COUNT(1) INTO selectNum FROM trading.asset WHERE stock_code = NEW.stock_code AND allocation = NEW.allocation AND account_id = NEW.account_id;
            IF selectNum = 0 THEN
                INSERT INTO trading.asset(account_id, stock_code, allocation, number) VALUES (NEW.account_id, NEW.stock_code, NEW.allocation, NEW.number);
            ELSE
                IF(NEW.operate IN ('turnIn', 'buy', 'apply', 'shareAllotment')) THEN
                    UPDATE trading.asset SET number = number + NEW.number WHERE stock_code = NEW.stock_code AND allocation = NEW.allocation AND account_id = NEW.account_id;
                ELSEIF(NEW.operate IN ('turnOut', 'sell')) THEN
                    UPDATE trading.asset SET number = number - NEW.number WHERE stock_code = NEW.stock_code AND allocation = NEW.allocation AND account_id = NEW.account_id;
                ELSEIF(NEW.operate = 'position') THEN
                    UPDATE trading.asset SET number = NEW.number WHERE stock_code = NEW.stock_code AND allocation = NEW.allocation AND account_id = NEW.account_id;
                ELSE
                END IF;
            END IF;
            -- 价格行为交易处理数据
            IF (NEW.allocation = 'price_action') THEN
                PERFORM trading.func_price_action(json_build_object('operate', NEW.operate, 'stock_code', NEW.stock_code, 'account_id', NEW.account_id, 'price', NEW.price, 'number', NEW.number,
                    'fee', NEW.commission + NEW.stamp_duty + NEW.transfer_fee, 'stop_losses', NEW.stop_losses, 'reasons', NEW.reasons, 'datetime', NEW.datetime));
            END IF;
            -- 统计持仓成本
            SELECT count(1) INTO selectNum FROM trading.position WHERE stock_code = NEW.stock_code AND account_id = NEW.account_id;
            IF (NEW.operate IN ('buy', 'apply', 'turnIn')) THEN
                -- 买入
                IF (selectNum  = 0) THEN
                    -- 建仓
                    INSERT INTO trading.position(account_id, stock_code, capitalized, number, initial_date)VALUES (NEW.account_id, NEW.stock_code, NEW.amount/NEW.number, NEW.number, CAST(NEW.datetime AS DATE));
                ELSE
                    -- 加仓
                    UPDATE trading.position p SET capitalized = (p.capitalized * p.number + NEW.amount)/(p.number + NEW.number), number = (p.number + NEW.number)
                                              WHERE p.stock_code = NEW.stock_code AND account_id = NEW.account_id;
                END IF;
            ELSEIF (NEW.operate IN ('sell', 'TurnOut')) THEN
                -- 卖出
                IF((NEW.positions < ABS(NEW.number))) THEN
                    RAISE EXCEPTION '计算持仓成本统计计算失败！' USING HINT = '卖出数量大于持仓数量', ERRCODE = '77248',
                        DETAIL = '持仓证券：' || NEW.stock_label || '，持仓数量：' || NEW.positions || '，卖出数量：' || NEW.number;
                END IF;
                IF(selectNum > 0 AND NEW.positions > 0) THEN
                    IF((NEW.positions - NEW.number) = 0) THEN
                        UPDATE trading.position p SET capitalized = 0, number = 0 WHERE p.stock_code = NEW.stock_code AND account_id = NEW.account_id;
                    ELSE
                        UPDATE trading.position p SET capitalized = (p.capitalized * p.number - NEW.amount)/(p.number - NEW.number), number = (p.number - NEW.number)
                                                  WHERE p.stock_code = NEW.stock_code AND account_id = NEW.account_id;
                    END IF;
                ELSE
                    RAISE EXCEPTION '计算持仓成本统计计算失败！' USING HINT = '不能空卖！', ERRCODE = '77249', DETAIL = '持仓中没有该证券：' || NEW.stock_label;
                END IF;
            ELSE
                -- 调仓
                UPDATE trading.position p SET capitalized = NEW.amount/NEW.number, number = NEW.number WHERE p.stock_code = NEW.stock_code AND account_id = NEW.account_id;
            END IF;
        ELSEIF(NEW.operate = 'stockDividend') THEN
            UPDATE trading.position p SET capitalized = (p.capitalized * p.number - NEW.amount)/p.number WHERE p.stock_code = NEW.stock_code AND account_id = NEW.account_id;
        ELSEIF(NEW.operate = 'shareAllotment') THEN
            UPDATE trading.position p SET capitalized = (p.capitalized * p.number)/(p.number + NEW.number), number = number + NEW.number
                                      WHERE p.stock_code = NEW.stock_code AND account_id = NEW.account_id;
        ELSE
            -- 其他操作
        END IF;
    ELSE
        -- 其他操作
    END IF;
    -- 记录账户余额的变化，进行日志记录
    IF (NEW.operate IN ('buy', 'apply', 'toBank')) THEN
        UPDATE trading.account SET balance = balance - NEW.amount WHERE uuid = NEW.account_id RETURNING balance INTO NEW.balance;
    ELSEIF (NEW.operate IN ('position', 'turnIn', 'turnOut')) THEN
    -- 不修改账户余额
        SELECT balance INTO NEW.balance FROM trading.account WHERE uuid = NEW.account_id;
    ELSE
        UPDATE trading.account SET balance = balance + NEW.amount WHERE uuid = NEW.account_id RETURNING balance INTO NEW.balance;
    END IF;
    -- 设定新持仓数量和持仓成本
    SELECT capitalized, number INTO NEW.capitalized, NEW.positions FROM trading.position WHERE stock_code = NEW.stock_code AND account_id = NEW.account_id;
    SELECT trading.func_format_abstracts(json_build_object('operate', NEW.operate, 'stock_label', NEW.stock_label, 'price', NEW.price, 'number',
        NEW.number, 'amount', NEW.amount, 'capitalized', NEW.capitalized, 'positions', NEW.positions)) INTO abstracts;
    IF (abstracts->>'status' = 'Success') THEN
        IF (NEW.operate IN ('buy', 'sell')) THEN
            SELECT label INTO NEW.abstracts FROM trading.dictionary WHERE code = NEW.allocation AND kind = 'asset.allocation';
            NEW.abstracts := CONCAT(NEW.abstracts, abstracts->>'msg');
            IF (NEW.allocation = 'price_action') THEN
                SELECT label INTO reasonsMsg FROM trading.dictionary WHERE code = NEW.reasons AND kind = NEW.operate || '.price.action';
                NEW.abstracts := CONCAT(NEW.abstracts, '，交易理由：', reasonsMsg, '，止损价格：￥', NEW.stop_losses, '，证券账户：', accountLabel, '，账户现金余额：￥', NEW.balance);
            ELSE
                NEW.abstracts := CONCAT(NEW.abstracts, '，证券账户：', accountLabel, '，账户现金余额：￥', NEW.balance);
            END IF;
        ELSE
            NEW.abstracts := CONCAT(abstracts->>'msg', '，证券账户：', accountLabel, '，账户现金余额：￥', NEW.balance);
        END IF;
    ELSE
        RAISE EXCEPTION '在计算账户余额的变化，进行日志记录时失败！' USING HINT = abstracts->>'msg', ERRCODE = SQLSTATE;
    END IF;
    RETURN NEW;
    EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS pgrn_MESSAGE = MESSAGE_TEXT, pgrn_DETAIL = PG_EXCEPTION_DETAIL, pgrn_HINT = PG_EXCEPTION_HINT, pgrn_CONTEXT = PG_EXCEPTION_CONTEXT, pgrn_SQLSTATE = RETURNED_SQLSTATE;
        RAISE NOTICE 'MESSAGE: %', pgrn_MESSAGE;
        RAISE NOTICE 'DETAIL:　%', pgrn_DETAIL;
        RAISE NOTICE 'HINT:　%', pgrn_HINT;
        RAISE NOTICE 'CONTEXT:　%', pgrn_CONTEXT;
        RAISE NOTICE 'SQLSTATE:　%', pgrn_SQLSTATE;
END
$$ LANGUAGE plpgsql;
COMMENT ON FUNCTION trading.trigger_insert_before_transaction_function() IS '【证券及ETF操作记录表】的 BEFORE 触发器函数';

CREATE TRIGGER trigger_insert_before_transaction BEFORE INSERT
  ON trading.transaction FOR EACH ROW EXECUTE PROCEDURE trading.trigger_insert_before_transaction_function();
COMMENT ON TRIGGER trigger_insert_before_transaction ON trading.transaction IS '【证券及ETF操作记录表】的 BEFORE 触发器';

DROP VIEW IF EXISTS trading.stock_view;
CREATE VIEW trading.stock_view AS
    SELECT stock_code,
           (CASE WHEN stock_market = '深圳证券交易所' THEN 'sz' || stock_code
                WHEN stock_market = '上海证券交易所' THEN 'sh' || stock_code
                WHEN stock_market = '北京证券交易所' THEN 'bj' || stock_code
           END) AS tdx_code,
           (CASE WHEN stock_market = '深圳证券交易所' THEN stock_code || '.sz'
                WHEN stock_market = '上海证券交易所' THEN stock_code || '.sh'
                WHEN stock_market = '北京证券交易所' THEN stock_code || '.bj'
           END) AS ths_code,
           stock_type,
           stock_label,
           stock_market,
           (CASE WHEN stock_market = '深圳证券交易所' THEN 0
                WHEN stock_market = '上海证券交易所' THEN 1
                WHEN stock_market = '北京证券交易所' THEN 2
           END) AS market,
           stock_securities,
           stock_industry,
           stock_area,
           tradable_share || '（亿股）' AS tradable_share,
           total_share || '（亿股）' AS total_share,
           CAST(date_listing AS DATE) AS date_listing,
           info_url
    FROM trading.stock;
COMMENT ON VIEW trading.stock_view IS '股票和基金数据视图';
COMMENT ON COLUMN trading.stock_view.stock_code IS '股票或基金代码';
COMMENT ON COLUMN trading.stock_view.tdx_code IS '通达信代码';
COMMENT ON COLUMN trading.stock_view.ths_code IS '同花顺代码';
COMMENT ON COLUMN trading.stock_view.stock_type IS '股票、基金或债券';
COMMENT ON COLUMN trading.stock_view.stock_label IS '股票或基金名称';
COMMENT ON COLUMN trading.stock_view.stock_market IS '股票或基金所属市场';
COMMENT ON COLUMN trading.stock_view.market IS '股票或基金所属市场标识';
COMMENT ON COLUMN trading.stock_view.stock_securities IS '股票或基金所属类型';
COMMENT ON COLUMN trading.stock_view.stock_industry IS '股票或基金所属行业';
COMMENT ON COLUMN trading.stock_view.stock_area IS '股票或基金所属地域';
COMMENT ON COLUMN trading.stock_view.tradable_share IS '流通股（亿股）';
COMMENT ON COLUMN trading.stock_view.total_share IS '总股本（亿股）';
COMMENT ON COLUMN trading.stock_view.date_listing IS '上市日期';
COMMENT ON COLUMN trading.stock_view.info_url IS '股票或基金F10地址';

DROP VIEW IF EXISTS trading.owner_stock_view;
CREATE VIEW trading.owner_stock_view AS
    SELECT p.uuid,
           p.account_id,
           p.stock_code,
           s.tdx_code,
           s.ths_code,
           s.stock_type,
           s.stock_label,
           s.stock_market,
           s.market,
           s.stock_securities,
           s.stock_industry,
           s.stock_area,
           s.tradable_share,
           s.total_share,
           s.date_listing,
           s.info_url,
           p.capitalized,
           p.market_value,
           p.number
    FROM trading.position p, trading.stock_view s
    WHERE s.stock_code = p.stock_code;
COMMENT ON VIEW trading.owner_stock_view IS '持仓股票和基金数据视图';
COMMENT ON COLUMN trading.owner_stock_view.uuid IS '主键';
COMMENT ON COLUMN trading.owner_stock_view.account_id IS '交易账户ID';
COMMENT ON COLUMN trading.owner_stock_view.stock_code IS '股票或基金代码';
COMMENT ON COLUMN trading.owner_stock_view.tdx_code IS '通达信代码';
COMMENT ON COLUMN trading.owner_stock_view.ths_code IS '同花顺代码';
COMMENT ON COLUMN trading.owner_stock_view.stock_type IS '股票、基金或债券';
COMMENT ON COLUMN trading.owner_stock_view.stock_label IS '股票或基金名称';
COMMENT ON COLUMN trading.owner_stock_view.stock_market IS '股票或基金所属市场';
COMMENT ON COLUMN trading.owner_stock_view.market IS '股票或基金所属市场标识';
COMMENT ON COLUMN trading.owner_stock_view.stock_securities IS '股票或基金所属类型';
COMMENT ON COLUMN trading.owner_stock_view.stock_industry IS '股票或基金所属行业';
COMMENT ON COLUMN trading.owner_stock_view.stock_area IS '股票或基金所属地域';
COMMENT ON COLUMN trading.owner_stock_view.tradable_share IS '流通股（亿股）';
COMMENT ON COLUMN trading.owner_stock_view.total_share IS '总股本（亿股）';
COMMENT ON COLUMN trading.owner_stock_view.date_listing IS '上市日期';
COMMENT ON COLUMN trading.owner_stock_view.info_url IS '股票或基金F10地址';
COMMENT ON COLUMN trading.owner_stock_view.capitalized IS '持仓成本';
COMMENT ON COLUMN trading.owner_stock_view.market_value IS '市场价格';
COMMENT ON COLUMN trading.owner_stock_view.number IS '持仓数量';

DROP VIEW IF EXISTS trading.position_view;
CREATE VIEW trading.position_view AS
SELECT a.uuid AS account_id,
       o.uuid AS owner_id,
       o.username,
       o.realname,
       o.email,
       o.mobile,
       a.account_label,
       a.balance,
       p.stock_code,
       s.stock_label,
       s.stock_market,
       s.stock_type,
       s.stock_industry,
       s.stock_area,
       p.capitalized,
       p.number,
       CAST(ABS(p.capitalized * p.number) AS MONEY) AS amount,
       p.initial_date
FROM trading.position p
       LEFT JOIN trading.stock s ON p.stock_code = s.stock_code
       LEFT JOIN trading.account a ON p.account_id = a.uuid
       LEFT JOIN trading.owner o on a.owner_id = o.uuid
WHERE p.number != 0;
COMMENT ON VIEW trading.position_view IS '证券及ETF持仓统计表试图';
COMMENT ON COLUMN trading.position_view.owner_id IS '用户ID';
COMMENT ON COLUMN trading.position_view.username IS '用户名';
COMMENT ON COLUMN trading.position_view.realname IS '真实姓名';
COMMENT ON COLUMN trading.position_view.mobile IS '手机号码';
COMMENT ON COLUMN trading.position_view.email IS '电子邮件';
COMMENT ON COLUMN trading.position_view.account_id IS '账户ID';
COMMENT ON COLUMN trading.position_view.account_label IS '账户名称';
COMMENT ON COLUMN trading.position_view.balance IS '账户名称';
COMMENT ON COLUMN trading.position_view.stock_code IS '证券代码';
COMMENT ON COLUMN trading.position_view.stock_label IS '证券名称';
COMMENT ON COLUMN trading.position_view.stock_market IS '所属市场';
COMMENT ON COLUMN trading.position_view.stock_type IS '股票、基金或债券';
COMMENT ON COLUMN trading.position_view.stock_industry IS '所属行业';
COMMENT ON COLUMN trading.position_view.stock_area IS '所属地区';
COMMENT ON COLUMN trading.position_view.capitalized IS '持仓成本';
COMMENT ON COLUMN trading.position_view.number IS '持仓数量';
COMMENT ON COLUMN trading.position_view.amount IS '持仓金额';
COMMENT ON COLUMN trading.position_view.initial_date IS '初始建仓日期';

DROP VIEW IF EXISTS trading.price_action_view;
CREATE VIEW trading.price_action_view AS
    SELECT g.uuid AS group_id,
           g.account_id,
           g.stock_code,
           s.stock_label,
           s.stock_market,
           s.stock_securities,
           s.stock_industry,
           s.stock_area,
           g.position_direction,
           (SELECT label FROM trading.dictionary WHERE code = g.position_direction) AS position_direction_msg,
           g.capitalized,
           g.positions,
           g.fee AS group_fee,
           g.profit,
           g.is_finish,
           g.last_datetime,
           p.uuid AS pa_uuid,
           p.operate,
           (SELECT label FROM trading.dictionary WHERE code = p.operate) AS operate_msg,
           p.method,
           (SELECT label FROM trading.dictionary WHERE code = p.method) AS method_msg,
           p.holdings,
           (SELECT label FROM trading.dictionary WHERE code = p.holdings) AS holdings_msg,
           p.reasons,
           (SELECT label FROM trading.dictionary WHERE code = p.reasons) AS reasons_msg,
           p.price,
           p.number,
           p.fee,
           p.cost_price,
           p.stop_losses,
           p.risk_taking,
           p.message,
           p.datetime
    FROM trading.price_action_group g
        LEFT JOIN trading.price_action p ON  g.uuid = p.group_id
        LEFT JOIN trading.stock s ON g.stock_code = s.stock_code;
COMMENT ON VIEW trading.price_action_view IS '价格行为交易系统统计表视图';
COMMENT ON COLUMN trading.price_action_view.group_id IS '交易组ID';
COMMENT ON COLUMN trading.price_action_view.account_id IS '账户ID';
COMMENT ON COLUMN trading.price_action_view.stock_code IS '证券代码';
COMMENT ON COLUMN trading.price_action_view.stock_label IS '证券名称';
COMMENT ON COLUMN trading.price_action_view.stock_market IS '所属市场';
COMMENT ON COLUMN trading.price_action_view.stock_securities IS '所属类型';
COMMENT ON COLUMN trading.price_action_view.stock_industry IS '所属行业';
COMMENT ON COLUMN trading.price_action_view.stock_area IS '所属地区';
COMMENT ON COLUMN trading.price_action_view.position_direction IS '头寸方向';
COMMENT ON COLUMN trading.price_action_view.position_direction_msg IS '头寸方向说明';
COMMENT ON COLUMN trading.price_action_view.capitalized IS '组持仓成本';
COMMENT ON COLUMN trading.price_action_view.positions IS '组持仓数量';
COMMENT ON COLUMN trading.price_action_view.group_fee IS '组交易费用';
COMMENT ON COLUMN trading.price_action_view.profit IS '组交易利润';
COMMENT ON COLUMN trading.price_action_view.is_finish IS '组是否交易完成';
COMMENT ON COLUMN trading.price_action_view.last_datetime IS '组最后交易时间';
COMMENT ON COLUMN trading.price_action_view.pa_uuid IS '价格行为交易ID';
COMMENT ON COLUMN trading.price_action_view.operate IS '交易类型';
COMMENT ON COLUMN trading.price_action_view.operate_msg IS '交易类型说明';
COMMENT ON COLUMN trading.price_action_view.method IS '交易方式';
COMMENT ON COLUMN trading.price_action_view.method_msg IS '交易方式说明';
COMMENT ON COLUMN trading.price_action_view.holdings IS '仓位交易模式';
COMMENT ON COLUMN trading.price_action_view.holdings_msg IS '仓位交易模式说明';
COMMENT ON COLUMN trading.price_action_view.reasons IS '交易理由';
COMMENT ON COLUMN trading.price_action_view.reasons_msg IS '交易理由说明';
COMMENT ON COLUMN trading.price_action_view.price IS '交易单价';
COMMENT ON COLUMN trading.price_action_view.number IS '交易数量';
COMMENT ON COLUMN trading.price_action_view.fee IS '交易费用';
COMMENT ON COLUMN trading.price_action_view.cost_price IS '交易成本价';
COMMENT ON COLUMN trading.price_action_view.stop_losses IS '初始止损价';
COMMENT ON COLUMN trading.price_action_view.risk_taking IS '交易风险金额';
COMMENT ON COLUMN trading.price_action_view.message IS '交易说明';
COMMENT ON COLUMN trading.price_action_view.datetime IS '交易时间日期';

DROP VIEW IF EXISTS trading.transaction_view;
CREATE VIEW trading.transaction_view AS
    SELECT
        t.account_id,
        a.account_label,
        o.uuid AS owner_id,
        o.username,
        o.realname,
        o.email,
        o.mobile,
        t.stock_code,
        t.stock_label,
        t.stock_market,
        t.operate,
        (SELECT label FROM trading.dictionary WHERE code = t.operate) AS operate_label,
        t.allocation,
        (SELECT label FROM trading.dictionary WHERE code = t.allocation) AS allocation_label,
        t.price,
        t.stop_losses,
        t.number,
        t.amount,
        t.balance,
        t.capitalized,
        t.reasons,
        (SELECT label FROM trading.dictionary WHERE code = t.reasons) AS reasons_label,
        t.positions,
        t.commission,
        t.stamp_duty,
        t.transfer_fee,
        t.abstracts,
        t.datetime
    FROM trading.transaction t
        LEFT JOIN trading.account a ON t.account_id = a.uuid
        LEFT JOIN trading.owner o on a.owner_id = o.uuid;
COMMENT ON VIEW trading.transaction_view IS '证券及ETF操作记录视图';
COMMENT ON COLUMN trading.transaction_view.account_id IS '交易账户ID';
COMMENT ON COLUMN trading.transaction_view.account_label IS '账户名称';
COMMENT ON COLUMN trading.transaction_view.owner_id IS '用户ID';
COMMENT ON COLUMN trading.transaction_view.username IS '用户名';
COMMENT ON COLUMN trading.transaction_view.realname IS '真实姓名';
COMMENT ON COLUMN trading.transaction_view.mobile IS '手机号码';
COMMENT ON COLUMN trading.transaction_view.email IS '电子邮件';
COMMENT ON COLUMN trading.transaction_view.stock_code IS '股票或基金代码';
COMMENT ON COLUMN trading.transaction_view.stock_label IS '股票或基金名称';
COMMENT ON COLUMN trading.transaction_view.stock_market IS '股票或基金所属市场';
COMMENT ON COLUMN trading.transaction_view.operate IS '交易类型';
COMMENT ON COLUMN trading.transaction_view.operate_label IS '交易类型Label';
COMMENT ON COLUMN trading.transaction_view.allocation IS '资产配置标签';
COMMENT ON COLUMN trading.transaction_view.allocation_label IS '资产配置标签Label';
COMMENT ON COLUMN trading.transaction_view.price IS '交易单价';
COMMENT ON COLUMN trading.transaction_view.stop_losses IS '初始止损';
COMMENT ON COLUMN trading.transaction_view.number IS '交易数量';
COMMENT ON COLUMN trading.transaction_view.amount IS '交易金额';
COMMENT ON COLUMN trading.transaction_view.balance IS '当前账户余额';
COMMENT ON COLUMN trading.transaction_view.capitalized IS '当前持仓成本';
COMMENT ON COLUMN trading.transaction_view.reasons IS '交易原因';
COMMENT ON COLUMN trading.transaction_view.reasons_label IS '交易原因Label';
COMMENT ON COLUMN trading.transaction_view.positions IS '当前持仓数';
COMMENT ON COLUMN trading.transaction_view.commission IS '交易佣金';
COMMENT ON COLUMN trading.transaction_view.stamp_duty IS '交易印花税';
COMMENT ON COLUMN trading.transaction_view.transfer_fee IS '交易过户费';
COMMENT ON COLUMN trading.transaction_view.abstracts IS '操作摘要';
COMMENT ON COLUMN trading.transaction_view.datetime IS '交易时间日期';

DROP PROCEDURE IF EXISTS trading.truncate_database();
CREATE OR REPLACE PROCEDURE trading.truncate_database()
AS $$
DECLARE
    pgrn_MESSAGE TEXT;
    pgrn_DETAIL TEXT;
    pgrn_HINT TEXT;
    pgrn_CONTEXT TEXT;
    pgrn_SQLSTATE TEXT;
BEGIN
    DROP VIEW IF EXISTS trading.owner_stock_view;
    RAISE NOTICE 'DROP VIEW IF EXISTS trading.transaction_view;                                 【操作成功】';
    DROP VIEW IF EXISTS trading.stock_view;
    RAISE NOTICE 'DROP VIEW IF EXISTS trading.transaction_view;                                 【操作成功】';
    DROP VIEW IF EXISTS trading.transaction_view;
    RAISE NOTICE 'DROP VIEW IF EXISTS trading.transaction_view;                                 【操作成功】';
    DROP VIEW IF EXISTS trading.position_view;
    RAISE NOTICE 'DROP VIEW IF EXISTS trading.position_view;                                    【操作成功】';
    DROP VIEW IF EXISTS trading.price_action_view;
    RAISE NOTICE 'DROP VIEW IF EXISTS trading.price_action_view;                                【操作成功】';
    DROP TABLE IF EXISTS trading.transaction;
    RAISE NOTICE 'DROP TABLE IF EXISTS trading.transaction;                                     【操作成功】';
    DROP TABLE IF EXISTS trading.position;
    RAISE NOTICE 'DROP TABLE IF EXISTS trading.position;                                        【操作成功】';
    DROP TABLE IF EXISTS trading.asset;
    RAISE NOTICE 'DROP TABLE IF EXISTS trading.asset;                                           【操作成功】';
    DROP TABLE IF EXISTS trading.details_week;
    RAISE NOTICE 'DROP TABLE IF EXISTS trading.details_week;                                    【操作成功】';
    DROP TABLE IF EXISTS trading.details_price;
    RAISE NOTICE 'DROP TABLE IF EXISTS trading.details_price;                                   【操作成功】';
    DROP TABLE IF EXISTS trading.price_action;
    RAISE NOTICE 'DROP TABLE IF EXISTS trading.price_action;                                    【操作成功】';
    DROP TABLE IF EXISTS trading.price_action_group;
    RAISE NOTICE 'DROP TABLE IF EXISTS trading.price_action_group;                              【操作成功】';
    DROP TABLE IF EXISTS trading.account;
    RAISE NOTICE 'DROP TABLE IF EXISTS trading.account;                                         【操作成功】';
    DROP TABLE IF EXISTS trading.owner;
    RAISE NOTICE 'DROP TABLE IF EXISTS trading.owner;                                           【操作成功】';
    DROP TABLE IF EXISTS trading.stock;
    RAISE NOTICE 'DROP TABLE IF EXISTS trading.stock;                                           【操作成功】';
    DROP TABLE IF EXISTS trading.dictionary;
    RAISE NOTICE 'DROP TABLE IF EXISTS trading.dictionary;                                      【操作成功】';
    DROP FUNCTION IF EXISTS trading.trigger_insert_after_transaction_function();
    RAISE NOTICE 'DROP FUNCTION trading.trigger_insert_after_transaction_function();            【操作成功】';
    DROP FUNCTION IF EXISTS trading.trigger_insert_before_transaction_function();
    RAISE NOTICE 'DROP FUNCTION trading.trigger_insert_before_transaction_function();           【操作成功】';
    DROP FUNCTION IF EXISTS trading.trigger_insert_before_stock_function();
    RAISE NOTICE 'DROP FUNCTION IF EXISTS trading.trigger_insert_before_stock_function();       【操作成功】';
    DROP FUNCTION IF EXISTS trading.func_format_abstracts(JSON);
    RAISE NOTICE 'DROP FUNCTION trading.func_format_abstracts(JSON);                            【操作成功】';
    DROP FUNCTION IF EXISTS trading.func_calculate_fee(JSON);
    RAISE NOTICE 'DROP FUNCTION trading.func_calculate_fee(JSON);                               【操作成功】';
    DROP FUNCTION IF EXISTS trading.func_price_action(JSON);
    RAISE NOTICE 'DROP FUNCTION IF EXISTS trading.func_price_action(JSON);                      【操作成功】';
    DROP FUNCTION IF EXISTS trading.getMonMaxDate(iyear integer, imonth integer);
    DROP FUNCTION IF EXISTS trading.getDateWeek(date);
    DROP FUNCTION IF EXISTS trading.intervalDay(date);
    DROP FUNCTION IF EXISTS trading.isleap(integer);
    DROP FUNCTION IF EXISTS trading.func_search_dictionary(JSON);
    RAISE NOTICE 'DROP FUNCTION IF EXISTS trading.func_search_dictionary(JSON);                 【操作成功】';
    DROP FUNCTION IF EXISTS trading.func_search_dictionary(VARCHAR, VARCHAR);
    RAISE NOTICE 'DROP FUNCTION IF EXISTS func_search_dictionary(VARCHAR, VARCHAR);             【操作成功】';
    DROP FUNCTION IF EXISTS trading.func_get_default_account(VARCHAR, VARCHAR);
    RAISE NOTICE 'DROP FUNCTION IF EXISTS trading.func_get_default_account(VARCHAR, VARCHAR);   【操作成功】';
    DROP FUNCTION IF EXISTS trading.func_user_password_check(VARCHAR, VARCHAR);
    RAISE NOTICE 'DROP FUNCTION IF EXISTS trading.func_user_password_check(VARCHAR, VARCHAR);   【操作成功】';
    DROP PROCEDURE IF EXISTS trading.truncate_database();
    RAISE NOTICE 'DROP PROCEDURE IF EXISTS trading.truncate_database();                         【操作成功】';
    DROP PROCEDURE IF EXISTS trading.truncate_logic_database();
    RAISE NOTICE 'DROP PROCEDURE IF EXISTS trading.truncate_logic_database();                   【操作成功】';
    RAISE NOTICE '清空数据库操作成功！！！';
    EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS pgrn_MESSAGE = MESSAGE_TEXT, pgrn_DETAIL = PG_EXCEPTION_DETAIL, pgrn_HINT = PG_EXCEPTION_HINT, pgrn_CONTEXT = PG_EXCEPTION_CONTEXT, pgrn_SQLSTATE = RETURNED_SQLSTATE;
        RAISE NOTICE 'MESSAGE: %', pgrn_MESSAGE;
        RAISE NOTICE 'DETAIL:　%', pgrn_DETAIL;
        RAISE NOTICE 'HINT:　%', pgrn_HINT;
        RAISE NOTICE 'CONTEXT:　%', pgrn_CONTEXT;
        RAISE NOTICE 'SQLSTATE:　%', pgrn_SQLSTATE;
END;
$$ LANGUAGE plpgsql;
COMMENT ON PROCEDURE trading.truncate_database() IS '清空数据库';

DROP PROCEDURE IF EXISTS trading.truncate_logic_database();
CREATE OR REPLACE PROCEDURE trading.truncate_logic_database()
AS $$
DECLARE
    pgrn_MESSAGE TEXT;
    pgrn_DETAIL TEXT;
    pgrn_HINT TEXT;
    pgrn_CONTEXT TEXT;
    pgrn_SQLSTATE TEXT;
BEGIN
  UPDATE trading.account SET balance = 0.00 WHERE uuid = trading.func_get_default_account('GadflyBSD', 'account.stock')->'data'->>'uuid';
  DROP VIEW IF EXISTS trading.position_view;
  RAISE NOTICE 'DROP VIEW IF EXISTS trading.position_view;                                    【操作成功】';
  DROP VIEW IF EXISTS trading.price_action_view;
  RAISE NOTICE 'DROP VIEW IF EXISTS trading.price_action_view;                                【操作成功】';
  DROP TABLE IF EXISTS trading.transaction;
  RAISE NOTICE 'DROP TABLE IF EXISTS trading.transaction;                                     【操作成功】';
  DROP TABLE IF EXISTS trading.position;
  RAISE NOTICE 'DROP TABLE IF EXISTS trading.position;                                        【操作成功】';
  DROP TABLE IF EXISTS trading.asset;
  RAISE NOTICE 'DROP TABLE IF EXISTS trading.asset;                                           【操作成功】';
  DROP TABLE IF EXISTS trading.price_action;
  RAISE NOTICE 'DROP TABLE IF EXISTS trading.price_action;                                    【操作成功】';
  DROP TABLE IF EXISTS trading.price_action_group;
  RAISE NOTICE 'DROP TABLE IF EXISTS trading.price_action_group;                              【操作成功】';
  DROP FUNCTION IF EXISTS trading.trigger_insert_after_transaction_function();
  RAISE NOTICE 'DROP FUNCTION trading.trigger_insert_after_transaction_function();            【操作成功】';
  DROP FUNCTION IF EXISTS trading.trigger_insert_before_transaction_function();
  RAISE NOTICE 'DROP FUNCTION trading.trigger_insert_before_transaction_function();           【操作成功】';
  DROP FUNCTION IF EXISTS trading.func_format_abstracts(JSON);
  RAISE NOTICE 'DROP FUNCTION trading.func_format_abstracts(JSON);                            【操作成功】';
  DROP FUNCTION IF EXISTS trading.func_calculate_fee(JSON);
  RAISE NOTICE 'DROP FUNCTION trading.func_calculate_fee(JSON);                               【操作成功】';
  DROP FUNCTION IF EXISTS trading.func_price_action(JSON);
  RAISE NOTICE 'DROP FUNCTION IF EXISTS trading.func_price_action(JSON);                      【操作成功】';
  DROP PROCEDURE IF EXISTS trading.truncate_database();
  RAISE NOTICE 'DROP PROCEDURE IF EXISTS trading.truncate_database();                         【操作成功】';
  DROP PROCEDURE IF EXISTS trading.truncate_logic_database();
  RAISE NOTICE 'DROP PROCEDURE IF EXISTS trading.truncate_logic_database();                   【操作成功】';
  RAISE NOTICE '清空数据库操作成功！！！';
  EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS pgrn_MESSAGE = MESSAGE_TEXT, pgrn_DETAIL = PG_EXCEPTION_DETAIL, pgrn_HINT = PG_EXCEPTION_HINT, pgrn_CONTEXT = PG_EXCEPTION_CONTEXT, pgrn_SQLSTATE = RETURNED_SQLSTATE;
        RAISE NOTICE 'MESSAGE: %', pgrn_MESSAGE;
        RAISE NOTICE 'DETAIL:　%', pgrn_DETAIL;
        RAISE NOTICE 'HINT:　%', pgrn_HINT;
        RAISE NOTICE 'CONTEXT:　%', pgrn_CONTEXT;
        RAISE NOTICE 'SQLSTATE:　%', pgrn_SQLSTATE;
END;
$$ LANGUAGE plpgsql;
COMMENT ON PROCEDURE trading.truncate_logic_database() IS '清空逻辑部分数据库';

INSERT INTO trading.stock(stock_code, stock_label, stock_industry, stock_area, tradable_share, total_share, date_listing)
SELECT "代码", "名称(7196)", "细分行业", "地区", "流通股(亿)", "总股本(亿)", CASE "上市日期" WHEN '--  ' THEN NULL ELSE "上市日期" END FROM trading.stock_temp WHERE 代码 !~ '^(52[23]|180)\d{3}$';